View Full Version : Last row of the worksheet
Jeevie
02-12-2011, 07:24 PM
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
02-12-2011, 08:01 PM
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
02-13-2011, 07:07 AM
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
02-13-2011, 07:13 AM
Another way.
With Sheet1
Var1 = .Cells(.Rows.Count).End(xlUp).Row
End With
Kenneth Hobs
02-13-2011, 08:29 AM
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
02-13-2011, 08:44 AM
Arghh! Typo
With Sheet1
Var1 = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
The number one being the column in question.
mdmackillop
02-13-2011, 09:21 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.