Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 has wrong timing using the worksheet_change macro | Excel Worksheet Functions | |||
cell to cell multiplications wrong in Excel (28.22 x 27=761.90) | Excel Discussion (Misc queries) | |||
Wrong result returned by UsedRange.Rows.Count | Excel Programming | |||
Macro selecting wrong cell range | Excel Programming | |||
Excel VBA - UsedRange problem | Excel Programming |