1

I am trying to create an array of all the products that my store sells, but in the list, there are an abundance of blank cells that aren't needed when the matrix is good and done. Is there a way to get rid of these blank cells before I construct it?

For my last project, I did

Range("A:A").Find("").Select
    Selection.ClearContents
    Selection.Delete Shift:=xlUp

but I don't think that would work for this one because I don't want to mess up the worksheet in any way.

Presently I have this:

Dim iRows as Long

Sheets("PR001C2531380000001").Activate
iRows = Cells(Rows.Count, 1).End(xlUp).Row + CLng(0)

Do While Range("A:A").Find("ITEM-NO").Offset(c + 1, 0) = iRows
c = c + 1
Loop

ReDim PrMat(0 To iRows)
d = 0
For d = 0 To UBound(PrMat())
Do While Range("A:A").Find("ITEM-NO").Offset(d + 1, 0).End(xlUp).Row + CLng(0)
PrMat(d) = Range("A:A").Find("ITEM-NO").Offset(d + 1, 0)
d = d + 1

I am thinking it's not possible, but even some validation that my thinking is off would be great.

John
  • 15
  • 3
  • You could choose Data -> Filter, and de-select the blanks for the column you want to pack. The action does not permanently delete the blank entries and is undone by choosing select all or clearing the filter. – rajah9 Dec 21 '15 at 19:32
  • During the code to iterate through the cells on within the workbook, you could put in code to check that there is data in the cell before adding it into the array. – Steven Dec 21 '15 at 19:38
  • 1
    Why not just add something like `If Not IsEmpty(Range("A:A").Find("ITEM-NO").Offset(d+1,0) Then ...` and continue then to set `PrMat(d)` – BruceWayne Dec 21 '15 at 20:05

1 Answers1

1

Using the same technique as Is it possible to fill an array with row numbers which match a certain criteria without looping?, you can form a 1D array of non blanks as so

`form the array
x = Filter(Application.Transpose(Application.Evaluate("=IF(LEN(A1:A100)>0,A1:A100,""x"")")), "x", False)
`return a 1D string
MsgBox Join(x, ",")
Community
  • 1
  • 1
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • I changed it to `PrMat = Filter(Application.Transpose(Application.Evaluate("=IF(LEN(A:A)>0,A:A,""PrMat()"")")), "PrMat", False)` but it still worked wonderfully, thanks! – John Dec 22 '15 at 15:37
  • @JohnElias glad to have helped :) – brettdj Dec 23 '15 at 01:55