PDA

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.

GTO
06-29-2010, 11:52 PM
@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