retain Selection
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? |
retain Selection
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? |
retain Selection
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? |
retain Selection
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? |
retain Selection
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? |
retain Selection
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? |
retain Selection
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? |
retain Selection
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 |
retain Selection
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 |
All times are GMT +1. The time now is 12:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com