View Full Version : Solved: Excel 2003 end(xlup) problem
Gtrain
06-29-2010, 11:13 PM
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
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
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
Aussiebear
06-29-2010, 11:29 PM
Where does your data in column A stop?
mikerickson
06-29-2010, 11:33 PM
Perhaps
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
Blade Hunter
06-29-2010, 11:35 PM
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
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
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
You want something like this:
If Range("A1").End(xlDown).Row = Rows.Count Then
LastRow = Rows.Count
Else
LastRow = Range("A" & Rows.Count).End(xlUp).Row
End If
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
Blade Hunter
06-30-2010, 12:16 AM
@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 :)
Gtrain
06-30-2010, 03:50 PM
Thanks for the replies guys, i used the find method
Tony Nilsson
07-01-2010, 02:56 AM
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
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
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
Tony Nilsson
07-01-2010, 03:01 AM
Why not do it with a matrix formula ?
=MAX(ROW(1:65535)*($A$1:$A$65535<>""))
:hi:
Tony
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
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.