Changing position of last used cell

[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.

 

[Gord Dibben]

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

 

[IanC]

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.

 

[Lynz]

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.

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

 

[IanC]

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

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