Previous Cell

[Andrew]

I know the answer maybe simple and it will make me feel
thick but how do I return to the previously selected cell.

If I move from Z100 to A1 - how do I quickly get back to
Z100 without scrolling etc.


Thanks


Andrew

 

[Guest]

Hi

You could type the address (Z100) in the Name Box (the box just above column
A and to the left of the formula bar)

 

[JulieD]

and press enter

Hi

You could type the address (Z100) in the Name Box (the box just above column
A and to the left of the formula bar)

 

[AndyP]

Thanks Andy
But I will not know the previous position - I want it go
back to my previous position without having to note down
the cell reference.


Thanks



Andrew P

 

[Guest]

Hi

I can't help you, then. It might be possible to do it with a worksheet
change macro, but I'm still learning macros!

 

[JE McGimpsey]

One way (using macros):

Put this in the ThisWorkbook code module of your workbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not rCurrent Is Nothing Then Set rPrevious = rCurrent
Set rCurrent = ActiveCell
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Not rCurrent Is Nothing Then Set rPrevious = rCurrent
Set rCurrent = Target
End Sub

Put this in a regular code module:

Public rPrevious As Range
Public rCurrent As Range

Public Sub GoToPrevious()
If Not rPrevious Is Nothing Then Application.GoTo rPrevious
End Sub

Assign GoToPrevious to a toolbar button or keyboard shortcut if desired.

 

[Andrew P]

Thanks -brilliant!

Is it easy to change this to the penultimate cell - that
is the one before the last one?

I won't ask again!


Thanks


Andrew

 

[JE McGimpsey]

not too difficult:

ThisWorkbook:

Private Sub Workbook_Open()
Set rPenultimate = ActiveCell
Set rPrevious = rPenultimate
Set rCurrent = rPenultimate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
UpdatePenultimate
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
UpdatePenultimate
End Sub



Regular code module:

Public rCurrent As Range
Public rPenultimate As Range
Public rPrevious As Range

Public Sub UpdatePenultimate()
Set rPenultimate = rPrevious
Set rPrevious = rCurrent
Set rCurrent = ActiveCell
End Sub

Public Sub GoToPenultimate()
If Not rPenultimate Is Nothing Then Application.GoTo rPenultimate
End Sub