Guys,
Just a quick one, I amusing 2003 and have a sheet with data up to row 65536.
who cares you ask..
well when i use
[vba]lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row[/vba]
My lastRow value returns 1 not 65536, i could write a simple if statement, ie if 1 then 65536
but i would like to know, if there are any other ways of handling this.
Thanks
G
Where does your data in column A stop?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Perhaps
[VBA]Dim bottomCell As Range
With ThisWorkbook.Sheets("Sheet1").Range("A:A")
Set bottomCell = .Find(What:="?*", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
If bottomCell Is Nothing Then Set bottomCell = .Cells(1, 1): Rem column is empty
End With[/vba]
Originally Posted by Gtrain
You want something like this:
[vba]
If Range("A1").End(xlDown).Row = Rows.Count Then
LastRow = Rows.Count
Else
LastRow = Range("A" & Rows.Count).End(xlUp).Row
End If
[/vba]
Basically if there is data all the way then going to the end in an upwards direction takes you to the next blank, in your case there are none.
@BladeHunter:
Greetings Dan,
If the last cells are filled, but there are any blank cells midway, I believe the .Find method Mike showed will return the correct results, whereas xlUp will stop at the cell before the first blank (running up from the bottom).
Mark
Good point, I missed that one
Thanks for the replies guys, i used the find method
Why not do it with a matrix formula ?
=MAX(ROW(1:65535)*($A$1:$A$65535<>""))
Tony
Posting Permissions
Reply With Quote
