Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bill
 
Posts: n/a
Default 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?


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
Gary Brown
 
Posts: n/a
Default

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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"