how to get the last range address (not the last cell)

[filo666]

Hi, I found several posts indicating how to get the last cell, however I
would like to get the last selected range,

ej.

I select range A1:F2, then I select cell J3, I would like to get a variable
with the A1:F2 address after I selected the cell J3.

Thanks

 

[Mike H]

Hi,

Try this. You don't of course need the message box that's just to
demonstrate it's working. Right click your sheet tab, view code and paste the
code in

Private lastRange As Range

Private Sub Worksheet_Activate()
Set lastRange = ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
MsgBox "Last selected cell was " & lastRange.Address & Chr(10) _
& "Current cell is " & Target.Address
Set lastRange = Target
End Sub


Mike

 

[Mike H]

I should have added that you then go to your worksheet and select cells,
multiple cell ranges or non contiguous ranges by holding down the CTRL key

Mike

Mike

 

[Peter T]

You could store current/previous selections as Range objects, Named ranges,
or string address. There are pro/con's with each method. An example storing
the range objects

' code in Thisworkbook module

Private Sub Workbook_Activate()
If TypeName(Selection) = "Range" Then
SetSel Selection
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

SetSel Target

End Sub

' code in a normal module
Public grLastSel As Range, grCurSel As Range

Sub SetSel(rSel As Range)

Set grLastSel = grCurSel
Set grCurSel = rSel

''' depending on what you want, maybe
If grLastSel Is Nothing Then
Set grLastSel = rSel
End If

End Sub

Sub abc()
msgbox grLastSel.Parent.Name & vbCr & grLastSel.Address(0, 0)
End Sub

You'll need to initialise things. Note range objects (& named ranges) can
become corrupted if entire rows/cols are deleted from any area. OTOH, string
address is limited to 255 and is "historical".

Regards,
Peter T