I am trying to have a for loop iterate over the populated section of column b on an excel spreadsheet. I am almost positive that I have the code correct but for some reason it gives me an invalid qualifier error at the .Row point in the code below. I am really unsure where I went wrong and any help would be greatly appreciated.
Set rng1 = Range("B2")
Set rng2 = Range("B2").End(xlDown)
For i = 1 To Sheet2.Range(rng1, rng2).Row.Count
You're almost there:
.Rows.Count
Update
Try this:
Dim lLastRow As Long
lLastRow = Sheet2.Range("B2").End(xlDown).Row
For i = 1 To lLastRow
Debug.Print Sheet2.Cells(i, 1)
Next
Many ways to do this, here is one:
Option Explicit
Sub Rowcount()
Dim i& , LastRow&
Dim Sh as Worksheet
Set Sh= thisworkbook.sheets("Sheet2")
with Sh
'Set rng1 = .Range("B2")
Set rng2 = .Range("B2").End(xlDown) 'this method gives the first empty cell, wich is not always the last Row , works if NO empty cell
lastRow = rng2.row ' the real last row with some empty cells is LastRow= .cells(.rows.count,2).end(xlup).row
For i = 1 To LastRow
...'do stuff
'Debug.Print .Cells(i, 1).value2
Next i
End With 'Sh
End Sub
You can also find the last row of populated data using
Sub lastRowTest()
Dim lastRow As Integer
Dim i As Integer
'edit: Patrick Lepelletier also mentions this method in his post
lastRow = Worksheets("Sheet1").Range("B65536").End(xlUp).Row 'finds the first non-empty cell, starting from the bottom
For i = 1 To lastRow
'do stuff on cells
Next i
End Sub
