I'm trying to restore the Selection that was in place at the time I started a
routine that changes the selection during its run. In other words I want
something like

dim saveSel as Range
Set saveSel = Application.Selection

.... do stuff that destroys the current selection then

saveSel.Select

And the problem with this is of course that the object to which saveSel
refers has been destroyed in the meantime. What I actually want is to have
saveSel be a range (or ref to a range) of cells that is set to whatever was
selected originally.

Any ideas?

It should work exactly as you have coded it. If, however, you are navigating
between workbook/worksheets then save that information as well.
--
Gary''s Student - gsnu200785


"DrFear" wrote:

I'm trying to restore the Selection that was in place at the time I started a
routine that changes the selection during its run. In other words I want
something like

dim saveSel as Range
Set saveSel = Application.Selection

... do stuff that destroys the current selection then

saveSel.Select

And the problem with this is of course that the object to which saveSel
refers has been destroyed in the meantime. What I actually want is to have
saveSel be a range (or ref to a range) of cells that is set to whatever was
selected originally.

Any ideas?

Dim saveSel as String
saveSel = Selection.Address
'do stuff
Range(saveSel).Select
(only works on active sheet)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




DrFear"
wrote in message
I'm trying to restore the Selection that was in place at the time I started a
routine that changes the selection during its run. In other words I want
something like

dim saveSel as Range
Set saveSel = Application.Selection
.... do stuff that destroys the current selection then
saveSel.Select

And the problem with this is of course that the object to which saveSel
refers has been destroyed in the meantime. What I actually want is to have
saveSel be a range (or ref to a range) of cells that is set to whatever was
selected originally.
Any ideas?

If all your work is being done on the same worksheet, this structure should
do what you want...

Sub YourMacro()
Selection.Name = "UniqueSavedName"
'
' <<<Your code goes here
'
Range("UniqueSavedName").Select
End Sub

If you are changing worksheets during your code, then this structure should
work...

Sub YourMacro()
Dim WS As Worksheet
Set WS = ActiveSheet
Selection.Name = "UniqueSavedName"
'
' <<<Your code goes here
'
WS.Select
Range("UniqueSavedName").Select
End Sub

Rick


"DrFear" wrote in message
...
I'm trying to restore the Selection that was in place at the time I
started a
routine that changes the selection during its run. In other words I want
something like

dim saveSel as Range
Set saveSel = Application.Selection

... do stuff that destroys the current selection then

saveSel.Select

And the problem with this is of course that the object to which saveSel
refers has been destroyed in the meantime. What I actually want is to
have
saveSel be a range (or ref to a range) of cells that is set to whatever
was
selected originally.

Any ideas?

Should work perhaps ... but doesn't. I wasn't clear enough: after changing
the selection, I actuallyu delete the columns containing the cells originally
selected before repopulating. So in my code saveCell does not even point to
Nothing, it just errors to object not existing.

I do navigate to another workbook during ... steps, but at the time of my
unsuccessful saveSel.Select, ActiveWorkbook and ActiveSheet have been
restored to where the original selection was.

It still seems to me the problem is Set does not create a range object but
creates a pointer to a range object, and that object gets destroyed.

If I could get the range of Application.Selection expressed as a text string
I could later restore it with Range(storedString).Select - but how would I
get that text string in the first place, and is this not a rather clumsy way
of doing things?

Thanks for any further suggestions!

"Gary''s Student" wrote:

It should work exactly as you have coded it. If, however, you are navigating
between workbook/worksheets then save that information as well.
--
Gary''s Student - gsnu200785


"DrFear" wrote:

I'm trying to restore the Selection that was in place at the time I started a
routine that changes the selection during its run. In other words I want
something like

dim saveSel as Range
Set saveSel = Application.Selection

... do stuff that destroys the current selection then

saveSel.Select

And the problem with this is of course that the object to which saveSel
refers has been destroyed in the meantime. What I actually want is to have
saveSel be a range (or ref to a range) of cells that is set to whatever was
selected originally.

Any ideas?

Thanks - exactly what I needed (see my redundant reply to Gary's Student,
crossed in the post)

"Jim Cone" wrote:


Dim saveSel as String
saveSel = Selection.Address
'do stuff
Range(saveSel).Select
(only works on active sheet)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




DrFear"
wrote in message
I'm trying to restore the Selection that was in place at the time I started a
routine that changes the selection during its run. In other words I want
something like

dim saveSel as Range
Set saveSel = Application.Selection
.... do stuff that destroys the current selection then
saveSel.Select

And the problem with this is of course that the object to which saveSel
refers has been destroyed in the meantime. What I actually want is to have
saveSel be a range (or ref to a range) of cells that is set to whatever was
selected originally.
Any ideas?

Hi Dr Fear,


Try something like

'==========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range

Set WB = ActiveWorkBook
Set Rng = Selection
Set rCell = ActiveCell

' \\ Your code

Application.Goto Reference:=Rng
rCell.Activate

End Sub
'<==========



---
Regards.
Norman


"DrFear" wrote in message
...
I'm trying to restore the Selection that was in place at the time I
started a
routine that changes the selection during its run. In other words I want
something like

dim saveSel as Range
Set saveSel = Application.Selection

... do stuff that destroys the current selection then

saveSel.Select

And the problem with this is of course that the object to which saveSel
refers has been destroyed in the meantime. What I actually want is to
have
saveSel be a range (or ref to a range) of cells that is set to whatever
was
selected originally.

Any ideas?

Hi Dr Fear,

Stripping the code to that which is
essential, try:


'==========
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range

Set Rng = Selection
Set rCell = ActiveCell

' \\ Your code

Application.Goto Reference:=Rng
rCell.Activate

End Sub
'<==========



---
Regards.
Norman

If you're changing workbooks and or worksheets, you'll have to activate those
first.

savesel.parent.parent.activate 'activate the workbook
savesel.parent.select 'select the worksheet
savesel.select 'select the range

or you could use:

application.goto savesel, scroll:=true 'or false???



DrFear wrote:

I'm trying to restore the Selection that was in place at the time I started a
routine that changes the selection during its run. In other words I want
something like

dim saveSel as Range
Set saveSel = Application.Selection

... do stuff that destroys the current selection then

saveSel.Select

And the problem with this is of course that the object to which saveSel
refers has been destroyed in the meantime. What I actually want is to have
saveSel be a range (or ref to a range) of cells that is set to whatever was
selected originally.

Any ideas?

--

Dave Peterson