Solved: VBA Return to Initial Selected Cell

[enrique63]

How do I return to the initial selected cell after running a macro that changes the selected cell?

[Bob Phillips]

Save the address before you move and re-select it afterwards.

[enrique63]

Thanks, I'm new to VBA. It seems like I would need a line before and after my code. How would you write that?

[X10A]

I think it may be something like this:


Dim InitialCell As Range

Set InitialCell = Selection

'Whatever code you are running goes here

InitialCell.Select

[GTO]

Greetings Enrique,

Just by example of using the address as Bob stated:

Sub SeloldCell()
Dim strInitialCell As String

'// before selecting other cells
strInitialCell = ActiveCell.Address

'// Do whatever that selects other cells...
Range("B20").Select
Selection.Interior.ColorIndex = 3

'// reselect the cell that was initially the active cell
Range(strInitialCell).Select

End Sub


...or by setting a reference to the activecell (before whatever else we're doing)

Sub SelOldCell_2()
Dim rngOldCell As Range
'// Set an object reference to the 'old' cell
Set rngOldCell = ActiveCell

Range("B20").Select
Selection.Interior.ColorIndex = 7

rngOldCell.Select
End Sub


...similar to what X10A showed. I would note that if you use Selection at the start, then if a range of cells were initially selected, the same range would get re-selected of course.

Although you did not ask, I would only mention that in most cases, selecting cells is not necessary, such like:

Sub SkipSelecting()

Range("I1").Select
MsgBox ActiveCell.Address

'// ...but in most cases, you really do not need to be selectting other //
'// cells/sheets.... //
Range("B20").Interior.ColorIndex = 12
End Sub

Hope that helps :-)

Mark

[enrique63]

Thanks Mark, X10A, and xld. Your suggestions did the trick!