Changing position of last used cell

  • Thread starter Thread starter IanC
  • Start date Start date
I

IanC

What tells Excel where the last cell is (accessed by ctrl-end)?

I have a number of spreadsheets where data has been entered then deleted but
ctrl-end goes to the last cell that ever had data in, not the last cell
currently occupied.

For example, one sheet has no data below row 144 or to the right of column
AF, but ctrl-end takes me to AK170. I've tried deleting entire rows from 145
down to 200 and entire columns from AG to AZ, but still the last used cell
appears to be AK170.

How do I change this?

I suppose one option would be to copy the relevant portions into a new
sheet, but there are a lot of named ranges that I'll have to recreate if I
do this.
 
On a sheet, select all rows below real data range and delete those
rows...........delete.........do not just clear contents.

Same for all columns right of real data range.

NOW..............save the workbook in order to reset the used range.


Gord Dibben MS Excel MVP
 
Ron, I tried your method which was easier to use but in most cases failed,
presumably because of formatting. In those cases I used your method Gord.
Although more time consuming to implement, it worked every time.

Thank you both.
 
What tells Excel where the last cell is (accessed by ctrl-end)?

I have a number of spreadsheets where data has been entered then deleted but
ctrl-end goes to the last cell that ever had data in, not the last cell
currently occupied.

For example, one sheet has no data below row 144 or to the right of column
AF, but ctrl-end takes me to AK170. I've tried deleting entire rows from 145
down to 200 and entire columns from AG to AZ, but still the last used cell
appears to be AK170.

How do I change this?

I suppose one option would be to copy the relevant portions into a new
sheet, but there are a lot of named ranges that I'll have to recreate if I
do this.
Click to expand...

You dont have a "set Print Area" on there do you as Ctrl End will send
you to the end of that even if you have deleted the rows.
Lyn
 
Lynz said:
You dont have a "set Print Area" on there do you as Ctrl End will send
you to the end of that even if you have deleted the rows.
Lyn
Click to expand...

No, it was down to formatting. In some cases there is a Pring Area, but
Ctrl-End took the cursor beyond this.
 
Back
Top