Getting wrong last cell when using usedrange in macro in Excel

  • Thread starter Thread starter JeffL
  • Start date Start date
J

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?
 
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
 
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
 
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
 
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)
 
Back
Top