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.

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

wrote in message
oups.com...
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.

On Sep 3, 1:33 am, "Peter T" <peter_t@discussions wrote:
wrote:
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.

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?


" wrote:

On Sep 3, 1:33 am, "Peter T" <peter_t@discussions wrote:
wrote:
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.

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

Mark Lincoln

On Oct 2, 11:43 am, Mike H. wrote:
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?



" wrote:
On Sep 3, 1:33 am, "Peter T" <peter_t@discussions wrote:
wrote:
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 -

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"?

"Mark Lincoln" wrote:

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

Mark Lincoln

On Oct 2, 11:43 am, Mike H. wrote:
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?



" wrote:
On Sep 3, 1:33 am, "Peter T" <peter_t@discussions wrote:
wrote:
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 -