Hi,
I've put this code together to try and put the row indices of the non-blank cells in colB into an array. It fails on ther line "For Each rCell In Range("B2" & Lastrow)" with a Global failure.
What have I done wrong?
I've put this code together to try and put the row indices of the non-blank cells in colB into an array. It fails on ther line "For Each rCell In Range("B2" & Lastrow)" with a Global failure.
What have I done wrong?
VBA Code:
Sub Nonblanks()
Dim Lastrow As Long
Dim rCell As Range
Dim MyArray() As Variant
Dim iLoop As Integer
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For Each rCell In Range("B2" & Lastrow)
ReDim Preserve MyArray(iLoop)
If IsEmpty(Range("B" & iLoop).Value) = False Then
MyArray(iLoop) = rCell.Row
End If
iLoop = iLoop + 1
Next rCell
Worksheets("Output").Range("A1").Resize(UBound(MyArray)).Value = Application.Transpose(MyArray)
Erase MyArray
End Sub