0

I am trying to run a Simple formula to remove blank entries from an array. The array I am using is loaded from a field in a data table I have set up in excel called TY. The name of this field is TY[L3 Number]. I want to keep this array dynamic, as it might change as user add or delete rows from the data table. The test data I am using has 218 rows, 210 of which have duplicate entries (which I will want to remove later), and 8 entries of "".

When running the macro, I get a run-time error 9 "subscript out of range" on the first line of my if statement.

I have literally spent hours trying to understand why VBA is giving me this error. My understanding is that this is due to an array not correctly sized to handle the data being passed to it. I have used the debug window to verify that both arrays are sized correctly.

I am pretty new to programming, and teaching myself as I go, but I just kind find the solution to this one on my own.

Sub BuildArray()

'   Load array

Dim MyArr()
Dim J As Long


'   Size array

MyArr() = Range("TY[L3 Number]")
ReDim NewArr(LBound(MyArr) To UBound(MyArr))

'   For Loop to search for Blanks and remove from Array
'   The Lbound and UBound parameters will be defined by the size of the TY[L3 Number] field in the TY Table

J = LBound(MyArr) - 1 ' Added this recently while testing the theory that J may start at the 2nd      index in the loop, did not help

For i = LBound(MyArr) To UBound(MyArr)
   If MyArr(i) <> "" Then '   This is where I error out
        J = J + 1
        NewArr(J) = MyArr(i)
  End If
Next i
ReDim Preserve NewArr(LBound(MyArr) To J)

'   Debug Window to show results of revised array.

Dim c As Long
For c = LBound(NewArr) To UBound(NewArr)
   Debug.Print NewArr(c)
Next
   Debug.Print "End of List"

End Sub
Community
  • 1
  • 1
ImmutableTuple
  • 49
  • 1
  • 2
  • 9

1 Answers1

1

Ranges are multidimensional arrays.. (i.e. MyArray(#,#) where the first index will be the row and the second the column.

Change 

 If MyArr(i) <> "" Then

to 

 If MyArr(i,1) <> "" Then

if your range have only one column, the second index will always be 1

ejlj
  • 175
  • 3
  • Try this instead ReDim NewArr(UBound(MyArr), 1) Ubound will be the amount of rows in the first Array. – ejlj Aug 01 '14 at 13:46
  • Also, you have to increase the value of J after assigning the value to the new array (so it assign it to the index 0... – ejlj Aug 01 '14 at 13:50
  • Good news is I no longer get an error (Thank you!). Bad news is I still have blank entries in my array (as seen in immediate window). Following is the revisions to the code: `J = LBound(MyArr) - 1 For i = LBound(MyArr) To UBound(MyArr) If MyArr(i, 1) <> "" Then J = J + 1 NewArr(J, 1) = MyArr(i, 1) End If Next i ReDim Preserve NewArr(LBound(MyArr) To UBound(MyArr), 1)` – ImmutableTuple Aug 01 '14 at 14:26
  • well, you redim your array to be the same size of the first one which means that (potentially) at least the last rows will be empty. One option would be assign the array to a range sort it and the assign the range to an array (keep track of how many values you've written to know what the size of the new array will be ). – ejlj Aug 01 '14 at 14:29
  • Thank you. I had considered that, but the objective is to keep the array in code. – ImmutableTuple Aug 01 '14 at 14:44
  • I see, if you're not going to copy back to the Sheet (to a range) the you can assign the values to a Collection (which will dynamically grow only and every time you add a value). Dim newArr as new Collection.... and in the loop NewArr.Add MyArr(i, 1) Note that the collection is NOT multidiemsional – ejlj Aug 01 '14 at 14:50
  • Eventually I wanted to pass if back to a range, but I need the array to do a few more things before I do. Since you helped me resolve the original question I had, I accepted it (in the interest of keeping this thread lean). I now have to figure out how to get the original intention of the function to work. – ImmutableTuple Aug 01 '14 at 15:01