Better way of finding last row on sheet

[Andibevan]

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

[tkstock]

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

You could also do something like

Please Login or Register  to view this content.

HTH

[Jim Cone]

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" <[email protected]>
wrote in message news:[email protected]...
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

[Roman]

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

[Jef Gorbach]

"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> 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 ??

[Bob Phillips]

"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> 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

[Andibevan]

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" <[email protected]> wrote in message
news:[email protected]...

"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> 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

[Ashman]

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
>
>
>