Hi,

Can anyone improve on this formula to find the last row of a sheet:-

Sheet1.Range("A65536").End(xlUp).Row

This keeps finding row 26 as the last row when I only have 3 rows of data.

Any ideas?

Andi

Andi,

Did you check cell A26 to see if it had a space or some non-printing character in it?

Regards,
Jim Cone
San Francisco, USA


"Andibevan"
wrote in message ...
Hi,
Can anyone improve on this formula to find the last row of a sheet:-
Sheet1.Range("A65536").End(xlUp).Row
This keeps finding row 26 as the last row when I only have 3 rows of data.
Any ideas?
Andi

Hi Andibevan,

I guess that the last cell is really in 26th row.
This should select it (the same as Ctrl+End)

sub selectlastcell()
Cells.SpecialCells(xlCellTypeLastCell).select
end sub

Save your work and use this to recover your really last cell:
(this will reset the undo operations memory!)

sub recoverlastcell()
ActiveSheet.UsedRange
end sub

"Andibevan" wrote in message
...
Hi,

Can anyone improve on this formula to find the last row of a sheet:-

Sheet1.Range("A65536").End(xlUp).Row

This keeps finding row 26 as the last row when I only have 3 rows of data.

Any ideas?

Andi



sounds likes cell(A26) an "invisible" space/etc character ??

Do you have formulas in cells 4 thru 26? This would cause your
problem.

You could also do something like


Code:
--------------------
Range("A1").Select
While Activecell.Value < ""
Selection.Offset(1,0).Select
Wend
--------------------


HTH


--
tkstock


------------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...o&userid=14443
View this thread: http://www.excelforum.com/showthread...hreadid=379635

"Andibevan" wrote in message
...
Hi,

Can anyone improve on this formula to find the last row of a sheet:-

Sheet1.Range("A65536").End(xlUp).Row

This keeps finding row 26 as the last row when I only have 3 rows of data.

I very much doubt it, this technique doesn't suffer the last used cell
problem that say Ctlr-End suffers. You must have something in A26. Try
?LEN(Range("A26")) in the immediate window

Thank-you all for your very helpful comments - I was missing the obvious (as
usual).

There was some hidden text in row 26 - now works fine

Thanks all

"Bob Phillips" wrote in message
...

"Andibevan" wrote in message
...
Hi,

Can anyone improve on this formula to find the last row of a sheet:-

Sheet1.Range("A65536").End(xlUp).Row

This keeps finding row 26 as the last row when I only have 3 rows of data.

I very much doubt it, this technique doesn't suffer the last used cell
problem that say Ctlr-End suffers. You must have something in A26. Try
?LEN(Range("A26")) in the immediate window

The first question is, is there anything in cell a26? This procedure will
stop at anything

Ashman

"Andibevan" wrote:

Hi,

Can anyone improve on this formula to find the last row of a sheet:-

Sheet1.Range("A65536").End(xlUp).Row

This keeps finding row 26 as the last row when I only have 3 rows of data.

Any ideas?

Andi