Last row of the worksheet

[Jeevie]

Hi

I wanted to know your thoughts on what would be an accurate way to get the last used row of worksheet containing data. Can I use the usedrange property or define a function that loops through rows till it reaches as blank row ?

Thanks in advance

[Kenneth Hobs]

One could use UsedRange but the usedrange includes cells that are formatted that might not contain data. That does not sound like what you want.

For the last row with data:
Sub Test_LastRow()
MsgBox LastRow
End Sub

Function LastRow() As Long
Dim r As Range
Set r = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
If r Is Nothing Then
LastRow = 1
Else
LastRow = r.Row
End If
End Function

[Jeevie]

Hi

I am currently using the following code and need to know your thoughts on the same.

With Sheet.UsedRange
Var1 = .Rows(.Rows.Count).Row
End With

Thanks

[Tinbendr]

Another way.

With Sheet1
Var1 = .Cells(.Rows.Count).End(xlUp).Row
End With

[Kenneth Hobs]

If you are just interested in the last row for a certain column, I would use this method.

MsgBox Range("A" & Rows.Count).End(xlUp).Row

[Tinbendr]

Arghh! Typo
With Sheet1
Var1 = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

The number one being the column in question.

[mdmackillop]

Hi

I am currently using the following code and need to know your thoughts on the same.

With Sheet.UsedRange
Var1 = .Rows(.Rows.Count).Row
End With

Thanks
Used range may not start at Row 1 or Column 1 to cannot be depended upon to return the desired result.
Try this to demonstrate
Sub Test()
Dim sh
Set sh = Sheets.Add
sh.Cells(10, 10).Resize(10) = "x"
MsgBox sh.UsedRange.Address
End Sub