Getting wrong last cell when using usedrange in macro in Excel
 

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?

Getting wrong last cell when using usedrange in macro in Excel
 

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

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"JeffL" wrote:

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?

Getting wrong last cell when using usedrange in macro in Excel
 

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


"Michael Arch" wrote:

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

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"JeffL" wrote:

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?

Getting wrong last cell when using usedrange in macro in Excel
 

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

On Tue, 23 Jun 2009 08:12:02 -0700, Michael Arch
wrote:

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

Getting wrong last cell when using usedrange in macro in Excel
 

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

--

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




"Michael Arch" wrote in message ...
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

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"JeffL" wrote:

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?

Getting wrong last cell when using usedrange in macro in Excel
 

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)



"JeffL" wrote in message
...
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?