Getting wrong last cell when using usedrange in macro in Excel

[JeffL]

I've been told that using ActiveSheet.UsedRange will reset the special cell
LastCell after deleting from a sheet, but Excel says the last cell is at Y102
when it should be Y10. There is nothing on row 102 at all. What's happening?

 

[Michael Arch]

This happens when the cells are formatted, although there is no data excel
thinks there is. To avoid this issue, you may want to delete all cells below
Y10 using the Ctrl - short cut is the fastest way.
Alternatively, you could use this:
iLastrow= Range("Y65536").end(xlup).Row

 

[Jacob Skaria]

OR...(for 2007 & 2003) where you have more number of rows

ActiveSheet.Cells(Rows.Count, "Y").End(xlUp).Row

If this post helps click Yes

 

[Gord Dibben]

The cells don't need to be formatted for Excel to treat them as used.

If the cells were cleared using "clear contents" Excel still thinks they are
used.


Gord Dibben MS Excel MVP

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/last.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Patrick Molloy]

i think it gets reset when the workbook is saved then re-opened.
I never use it becaus eits not a safe way to determine the last row used.
use

1)
ActiveSheet.Cells(Rows.Count, "Y").End(xlUp).Row

or
2) more specific
..range("A1").End(xlDown)