How to change where ctrl-End goes (last cell of spreadsheet)?

[nomail1983]

I imported a large amount of data, then used a recorded macro to clean
up the spreadsheet by deleting rows. But ctrl-End still goes to some
cell far beyond the lower-right of the current data.

How can I change where ctrl-End goes to?

I would appreciate both VBA and Excel keyboard instructions. I intend
to incorporate this into my clean-up macro. But I would like to be
able to do this manually sometimes.

 

[Peter T]

Assuming apart from values there were no formats in any rows/columns
below/to-right of those you deleted, unfortunately, the only way to reset
the 'Last cell' is to programmatically call .UsedRange, eg simply

ActiveSheet.UsedRange ' typically works but not in all scenarios

Manually the only way I know to reset the Last Cell is to save the file.

Regards,
Peter T

 

[nomail1983]

How can I change where ctrl-End goes to?

[....]
ActiveSheet.UsedRange ' typically works but not in all scenarios

Works great for me. Just want I needed. Thanks much.

 

[Mike H.]

I wrote this sub:

Sub ResetUsedRange()
ActiveSheet.UsedRange
End Sub

Then i place my cursor in the cell I want to be the end and execute the
macro and nothing changes. Ideas?

How can I change where ctrl-End goes to?

[....]
ActiveSheet.UsedRange ' typically works but not in all scenarios

Works great for me. Just want I needed. Thanks much.

 

[Mark Lincoln]

UsedRange is read-only. You need to enter something in the cell you
want to be the "end" to make it so.

Mark Lincoln

I wrote this sub:

Sub ResetUsedRange()
ActiveSheet.UsedRange
End Sub

Then i place my cursor in the cell I want to be the end and execute the
macro and nothing changes. Ideas?

How can I change where ctrl-End goes to?
[....]
ActiveSheet.UsedRange ' typically works but not in all scenarios

Works great for me. Just want I needed. Thanks much.- Hide quoted text -

- Show quoted text -

 

[Mike H.]

Okay, so I enter something in the cell I wish to make the last cell and then
execute the sub and nothing happens. Am I missing something or how do you
"Make it so"?

UsedRange is read-only. You need to enter something in the cell you
want to be the "end" to make it so.

Mark Lincoln

I wrote this sub:

Sub ResetUsedRange()
ActiveSheet.UsedRange
End Sub

Then i place my cursor in the cell I want to be the end and execute the
macro and nothing changes. Ideas?

How can I change where ctrl-End goes to?
[....]
ActiveSheet.UsedRange ' typically works but not in all scenarios

Works great for me. Just want I needed. Thanks much.- Hide quoted text -

- Show quoted text -

 

[Peter T]

Normally the 'LastCell' is the intersection of the lower-most row and
right-most column that each contain data and/or some formatting. Row height
counts as a format for this purpose, however entire rows/columns of
identical formatting do not (except where they intersect). The last-cell
itself might be totally empty of both data and formats. The LastCell can be
selected by pressing Ctrl-End or referenced by .SpecialCells(xlLastCell).

Say currently the Last Cell is "J10", If columns "H:J" and rows 8:10 are
deleted (ie all data & all formats removed), one might reasonably expect the
LastCell to become "G7". But it doesn't, it remains as "J10".

Usually the LastCell can be 'corrected' to G7 with any use of .UsedRange, as
in the example I gave, which appears to work fine for the OP.

As Mark says UsedRange is Read-only, however it does serve to reset the used
range. Very occasionally it doesn't reset some worksheets where saving the
wb would.

Regards,
Peter T

Okay, so I enter something in the cell I wish to make the last cell and then
execute the sub and nothing happens. Am I missing something or how do you
"Make it so"?

UsedRange is read-only. You need to enter something in the cell you
want to be the "end" to make it so.

Mark Lincoln

I wrote this sub:

Sub ResetUsedRange()
ActiveSheet.UsedRange
End Sub

Then i place my cursor in the cell I want to be the end and execute the
macro and nothing changes. Ideas?



:
How can I change where ctrl-End goes to?
[....]
ActiveSheet.UsedRange ' typically works but not in all scenarios

Works great for me. Just want I needed. Thanks much.- Hide quoted text -

- Show quoted text -