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.

 

[Ron Rosenfeld]

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

There is a property called UsedRange

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 can change this with a VBA Macro. You should also be aware that
any cells that have any kind of format applied will still be
considered part of the UsedRange.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select
the macro by name, and <RUN>.

================================
Sub ResetUsedRange()
ActiveSheet.UsedRange
End Sub
=============================

 

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

 

[Ron Rosenfeld]

Ron, I tried your method which was easier to use but in most cases failed,
presumably because of formatting.

Yes -- it does depend on there being nothing past that point.

In those cases I used your method Gord.
Although more time consuming to implement, it worked every time.

Thank you both.

Ian,

Gord's method can be done via a VBA macro

To enter this Macro (Sub), <alt-F11> opens the Visual Basic
Editor. Ensure your project is highlighted in the Project
Explorer window. Then, from the top menu, select
Insert/Module and paste the code below into the window that
opens.

To use this Macro (Sub), first select the cell that you wish to
represent the Last Cell.

<alt-F8> opens the macro dialog
box. Select the macro by name, and <RUN>.

The macro will give you an opportunity to confirm the deletion,
because the deletions are irreversible.

=======================================
Option Explicit
Sub ChangeLastUsed()
Dim c As Range
Dim DeleteOK As Integer
Set c = Selection
Range(c(2), c(Cells.Rows.Count - c.Row)).EntireRow.Select
DeleteOK = MsgBox("Confirm Irreversible Deletion of selected
rows", vbYesNoCancel)
If DeleteOK = vbYes Then
Selection.Delete
Else
c.Select
Exit Sub
End If
Range(c(, 2), c(, Cells.Columns.Count - c.Column)).EntireColumn.Select
DeleteOK = MsgBox("Confirm Irreversible Deletion of Selected
Columns", vbYesNoCancel)
If DeleteOK = vbYes Then
Selection.Delete
Else
c.Select
End If
End Sub
=======================================

 

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

 

[Graham Brown]

Little bit late but this works - info picked up from this site.

select the last cell in row and column that you require then run the following macro. (you can save the macro in your personal.xlsb workbook for future use.

NOTE: once run just save the workbook and last cell will be the one you selected. This is a good way to reduce file size.

Sub ChangeLastUsed()
Dim c As Range
Dim DeleteOK As Integer
Set c = Selection
Range(c(2), c(Cells.Rows.Count - c.Row)).EntireRow.Select
DeleteOK = MsgBox("Confirm Irreversible Deletion of selected Rows ", vbYesNoCancel)
If DeleteOK = vbYes Then
Selection.Delete
Else
c.Select
Exit Sub
End If
Range(c(, 2), c(, Cells.Columns.Count - c.Column)).EntireColumn.Select
DeleteOK = MsgBox("Confirm Irreversible Deletion of Selected Columns ", vbYesNoCancel)
If DeleteOK = vbYes Then
Selection.Delete
Else
c.Select
End If
End Sub