Retaining Selected area
 

A question was posed earlier today about how to retain the address of the
activecell and Bob Philips gave this as a solution:
Set oCell = Activecell
' do your stuff elsewhere
oCell.Select
Is there a way for one to retain a selected area, do a few things and then
go back to the original area selected?

Set oRange=Selection and oRange.Select after doing other things did not seem
to work for me --but that might be my ineptitude.

Specifically, I would like to delete all items other than an activecell in a
selected range. I am able to do this manually but am not able to record it --
as the recorder doesn't seem to record CTRL + . (period) satisfactorily. The
way I do it manually is:
Press Ctrl + .
Shift + down arrow
Press Delete key
Shift + Up arrow

Thank you for your help.

Retaining Selected area
 

Elaine,

How about:

Dim myVal As Variant

myVal = ActiveCell.Value
Selection.ClearContents
ActiveCell.Value = myVal

HTH,
Bernie
MS Excel MVP

"Elaine" wrote in message
...
A question was posed earlier today about how to retain the address of the
activecell and Bob Philips gave this as a solution:
Set oCell = Activecell
' do your stuff elsewhere
oCell.Select
Is there a way for one to retain a selected area, do a few things and then
go back to the original area selected?

Set oRange=Selection and oRange.Select after doing other things did not
seem
to work for me --but that might be my ineptitude.

Specifically, I would like to delete all items other than an activecell in
a
selected range. I am able to do this manually but am not able to record
it --
as the recorder doesn't seem to record CTRL + . (period) satisfactorily.
The
way I do it manually is:
Press Ctrl + .
Shift + down arrow
Press Delete key
Shift + Up arrow

Thank you for your help.

Retaining Selected area
 

Thank you very much Bernie. This is what I wanted.

I also wanted to say that Bob Philips' code worked for retaining selections
as well. As usual it was my mistake.

"Bernie Deitrick" wrote:

Elaine,

How about:

Dim myVal As Variant

myVal = ActiveCell.Value
Selection.ClearContents
ActiveCell.Value = myVal

HTH,
Bernie
MS Excel MVP

"Elaine" wrote in message
...
A question was posed earlier today about how to retain the address of the
activecell and Bob Philips gave this as a solution:
Set oCell = Activecell
' do your stuff elsewhere
oCell.Select
Is there a way for one to retain a selected area, do a few things and then
go back to the original area selected?

Set oRange=Selection and oRange.Select after doing other things did not
seem
to work for me --but that might be my ineptitude.

Specifically, I would like to delete all items other than an activecell in
a
selected range. I am able to do this manually but am not able to record
it --
as the recorder doesn't seem to record CTRL + . (period) satisfactorily.
The
way I do it manually is:
Press Ctrl + .
Shift + down arrow
Press Delete key
Shift + Up arrow

Thank you for your help.