Cntrl + End (xlLastCell)
 

I want to use Cntrl + End to find that last cell containing data in my
spreadsheet. I recorded this action and the following VBA command was the
result.

ActiveCell.SpecialCells(xlLastCell).Select

It jumps to an empty cell. After some experimentation, I discovered that
Excel is jumping to the furthest cell to the bottom right that had ever
contained data, regardless of whether it actually contains data at the
moment. I concluded this by picking a cell a few rows down and columns
right of the cell Excel selected with Cntrl+End, entering some text, then
deleting it. Then when I issued the command again it took me to that new
cell. Deleting rows and columns had no effect.

How can I make Excel forget that I had used a particular cell so that Cntrl
+ End (xlLastCell) takes me to the last cell that presently contains data?

Visit Debra Dalgleish's site for some techniques:

http://www.contextures.com/xlfaqApp.html#Unused

Bill wrote:

I want to use Cntrl + End to find that last cell containing data in my
spreadsheet. I recorded this action and the following VBA command was the
result.

ActiveCell.SpecialCells(xlLastCell).Select

It jumps to an empty cell. After some experimentation, I discovered that
Excel is jumping to the furthest cell to the bottom right that had ever
contained data, regardless of whether it actually contains data at the
moment. I concluded this by picking a cell a few rows down and columns
right of the cell Excel selected with Cntrl+End, entering some text, then
deleting it. Then when I issued the command again it took me to that new
cell. Deleting rows and columns had no effect.

How can I make Excel forget that I had used a particular cell so that Cntrl
+ End (xlLastCell) takes me to the last cell that presently contains data?

--

Dave Peterson

Bill,
Just prior to the code you've got, put...
ActiveSheet.UsedRange
This re-sets Excel to know where the 'real' last cell with data currently
in it is (provided there are no cells further along that have formatting in
them).
Hope this helps,
Gary Brown


"Bill" wrote in message
...
I want to use Cntrl + End to find that last cell containing data in my
spreadsheet. I recorded this action and the following VBA command was the
result.

ActiveCell.SpecialCells(xlLastCell).Select

It jumps to an empty cell. After some experimentation, I discovered that
Excel is jumping to the furthest cell to the bottom right that had ever
contained data, regardless of whether it actually contains data at the
moment. I concluded this by picking a cell a few rows down and columns
right of the cell Excel selected with Cntrl+End, entering some text, then
deleting it. Then when I issued the command again it took me to that new
cell. Deleting rows and columns had no effect.

How can I make Excel forget that I had used a particular cell so that
Cntrl
+ End (xlLastCell) takes me to the last cell that presently contains data?