Extract row indexes of non-empty cells in specific range

[Westbury]

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?

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

 

[Fluff]

Try

For Each rCell In Range("B2:B" & Lastrow)

 

[Westbury]

Thanks Fluff. I've made that edit but now get the same type of failure at

If IsEmpty(Range("B" & iLoop).Value) = False Then

why is this happpening?

 

[Westbury]

perhaps I've found the problem...

If IsEmpty(rCell) = False Then

this appears to work but further down I get an error "overflow runtime 6". This file has almost 290,000 rows. Would puttng the whole worksheet into an array avoid this error?

 

[Fluff]

You will need to make iLoop a Long rather than an integer.
With 290,000 rows pulling col B into an array & looping through that would be a lot quicker.