Splitting Range into it's components

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

I used vba to make a range selection. When I do the following in the
immediate window

?Selection.address
$E$3:$DC$62

Which is great, but I need to split this into it's various component. IE:
Start Col = E, Start Row = 3, End Col = DC and End Row = 62. How can I do
this without reinventing the wheel? I'm assuming the is a built in method
that I am simply unaware of.

Thank you,

QB
 
One way... (note it rueurns the column number instead of letter)

With Selection(1)
MsgBox .Row
MsgBox .Column
End With
With Selection(Selection.Cells.Count)
MsgBox .Row
MsgBox .Column
End With
 
Nothing built-in, but easy to do...

Dim StartCol As String, EndCol As String
Dim StartRow As Long, EndRow As Long
Dim Addr() As String
With Selection
Addr = Split(Replace(.Address(1, 0), ":", "$"), "$")
StartCol = Addr(0)
StartRow = Addr(1)
EndCol = Addr(UBound(Addr) - 1)
EndRow = Addr(UBound(Addr))
End With
 
Back
Top