PDA

View Full Version : [SOLVED] How to Clear the ?Last Cell?



Cyberdude
06-06-2007, 02:41 PM
If I go to the ?Last Cell? on a worksheet by clicking EDIT->GOTO->SPECIAL->LAST CELL, I see a cell highlighted. On this particular worksheet, there is nothing in the ?Last Cell? or anything else on the screen being displayed. The address of the ?Last Cell? on this sheet is ?HZ111?. Since nothing is apparently there, why is this cell called the ?Last Cell??

My objective is to clear everything from the area of the sheet that I?m not using. If I select the ?Last Cell? and press the DELETE button, nothing seems to happen. I closed the workbook, reopened it, then did the GOTO LASTCELL sequence, and it takes me to ?HZ111? again. Why is it choosing that cell to display?

johnske
06-06-2007, 02:52 PM
special cells uses the 'used range' and the used range means 'used in any way whatsoever' (even if unseen - i.e. this includes formatting etc) so HZ111, or a row or column it intersects with may have formatting applied to it. Literally, last cell refers to the last cell in the used range.

Check HZ111 for such things and remove all formatting etc. but note that these changes to the used range only take effect after the workbook's been saved :)

Bob Phillips
06-06-2007, 04:16 PM
special cells uses the 'used range' and the used range means 'used in any way whatsoever' (even if unseen - i.e. this includes formatting etc) so HZ111, or a row or column it intersects with may have formatting applied to it. Literally, last cell refers to the last cell in the used range.

It also means may have been used previously but isn't now (a bug!)

johnske
06-06-2007, 05:27 PM
If you use this

MsgBox Sheet3.Cells.SpecialCells(xlCellTypeLastCell).Address
and then this

MsgBox Split(Sheet3.UsedRange.Address, ":")(1)
you'll see they're equivalent...

Cyberdude
06-07-2007, 05:06 PM
may have formatting applied to it. You bring another question to mind: If I format a cell's borders by using the "None" option, is the cell now considered to be formatted? I use that option a lot to get rid of residual borders that sometimes appear myteriously after I move a cell's contents. Perhaps that's what the invisible formatting is. But how can I get rid of unwanted borders without applying a border with the same color as the background?? :boohoo

johnske
06-07-2007, 05:42 PM
You bring another question to mind: If I format a cell's borders by using the "None" option, is the cell now considered to be formatted? ...:boohooWithout checking - most probably. After restoring the cells back to the original you must then SAVE to reset the used range. That's the 'bug' that Bob was referring to...

Ebrow
06-08-2007, 12:58 PM
FYI: there is a difference between pressing the delete button and right clicking and selecting delete.

Pressing the delete button mearly clears the contents, the VALUE, and doesn't revert the status back to an unused cell.

Right Clicking and selecting delete (shift cell up) will completly remove the cell and will no longer be a part of your used range.

I think i saw a KB on how to mange you Spreadsheet size. It should explain this concept.

Cyberdude
06-08-2007, 02:51 PM
Thanx, Ebrow ... that seems to work.
Sid