Cntrl + End (xlLastCell)

[Bill]

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?

[firefytr]

Hi,

You've found an (annoying) issue with Excel that plagues many! You need to run some VBA on it (Visual Basic for Applications - e.g. aka a macro). A simple routine such as ...

Sub ExcelDiet() 
    Dim x As Long
    Dim LastRow As Long
    Dim LastCol As Long
    Application.ScreenUpdating = False 
    LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _ 
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
    LastCol = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _ 
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 
    Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete 
    Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete 
    Application.ScreenUpdating = True 
End Sub

This particular code was written by DRJ and can be found here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

The code in the link was written to do all sheets in the workbook. The link also has instructions, a description, how to steps and a downloadable example file. Another very quick method would be to download/install ASAP Utilities (www.asap-utilities.com) which has a routine for this. Very simple, very quick. A lot of additional 'goodies' are contained with that add-in as well.

Run this code (Alt + F11 --> Insert --> Module, paste on right, press F5 to run) to reset the lastCell in Excel.

HTH

[Dave Peterson]

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

[Gary Brown]

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" <sorry_no_spam@aol.com> wrote in message
news:41f18b7b$1@usenet01.boi.hp.com...
>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?
>
>

[psmart]

Really simple
ActiveSheet.UsedRange
This will reset the xlLastCell to the correct position