MrExcel Publishing
Your One Stop for Excel Tips & Solutions

No Home on the Range

Posted by John Latasa on September 23, 2000 3:30 PM

When I select a cell in the upper left corner of a
range of cells with data in them. I can select the entire
range by hitting Shift+End+Home.

However, Excel does something peculiar (which does not happen
in Lotus 1-2-3, by the way). If I type in a cell outside
of the data range and then delete that data, Excel then
includes this cell in the range when I do the Shift+End+Home.

The same thing happens with the SpecialCells(xlLastCell)
command in Visual Basic.

Is there any way to avoid selecting cells which are now blank,
but had data previously? I would like to select only cells that
have something in them.

Posted by Celia on September 23, 2000 6:17 PM

The following site has a way of resetting the last cell with an event procedure :-

Instead of an event procedure, I use the following attached to a toolbar button :-
Sub ResetLastCell()
Dim x As Integer
x = ActiveSheet.UsedRange.Rows.Count
End Sub


Posted by Ivan Moala on September 24, 2000 1:35 AM

Just adding to Celias macro, any macro run
with the UsedRange comand will reset this....
in earlier versions of excel you could save the workbook
to reset it and release the RAM not being used.