Extract row indexes of non-empty cells in specific range

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
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?

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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,776
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
For Each rCell In Range("B2:B" & Lastrow)
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
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

Board Regular
Joined
Jun 7, 2009
Messages
136
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,776
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,122,263
Messages
5,595,166
Members
413,973
Latest member
leon1974mk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top