find last row

[mithu81]

Question.

I am using this code to find the last row with data.

r=2
do while r < 65536 and Len(Cells(r,1).text) > 0
r = r+1
loop

Last row = r


but here is the problem. I have some gaps in the rows.. so row 23 might not have any data in it but row 24 does.. is there any other, "more efficient" way to find the last row ?

 

[texasalynn]

try this

LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

 

[Colin Legg]

For simple situations the common way is to use the Range object's End property:

Sub foo()
    Dim r As Long
    
    r = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    
    
End Sub

 

[mithu81]

Thank you