VBA Finding corners of a selection

Question

Given a rectangular Selection, how do I find the Cells for each corner? Specifically, the top right and bottom left.

E.g. if a user selects the range B2:G9, I would like to get the address of each corner from the Selection object.

I used .Address to get the top left and the bottom right corners, and while I could start splitting the strings and perform regex replace on them I want to know if there is a cleaner way.

Answer 1

    top_right_row=selection.rows(1).row
    top_right_col=(selection.columns(1).column+selection.columns.count-1)

    bottom_left_row=(selection.rows(1).row+selection.rows.count-1)
    bottom_left_col=selection.columns(1).column

you can get row and column values like this. i havent tested the code if you have any error please revert back

Answer 2

Do you mean something like the code below.

Note: it's better to stay away from Select and Selection, you could try using With Range("B2:G9") instead (not implemented yet in the code below)

Option Explicit

Sub GetRangeSelectionCorners()

Dim TopLeft As String, TopRight As String, BottomLeft As String, BottomRight As String
Dim TopLeftRow As Long, TopLeftCol As Long, BottomRightRow As Long, BottomRightCol As Long

Range("B2:G9").Select
With Selection
    TopLeft = .Cells(1, 1).Address '<-- top left cell in Selection
    TopRight = .Cells(1, .Columns.Count).Address '<-- top right cell in Selection
    BottomLeft = .Cells(.Rows.Count, 0.1).Address '<-- bottom left cell in selection
    BottomRight = .Cells(.Rows.Count, .Columns.Count).Address '<-- last cell in selection (bottom right)

    ' get row and column number
    TopLeftRow = .Cells(1, 1).Row '<-- top left cell's row
    TopLeftCol = .Cells(1, 1).Column '<-- top left cell's column
    BottomRightRow = .Cells(.Rows.Count, .Columns.Count).Row '<-- bottom right cell's row
    BottomRightCol = .Cells(.Rows.Count, .Columns.Count).Column  '<-- bottom right cell's column
End With

MsgBox "Top Left cell address is :" & TopLeft & vbCr & _
        "Top Right cell address is :" & TopRight & vbCr & _
        "Bottom Left cell address is :" & BottomLeft & vbCr & _
        "Bottom Right cell address is :" & BottomRight

MsgBox "Top Left cell's row is : " & TopLeftRow & _
        ", and column is :" & TopLeftCol & vbCr & _
        "Bottom Right cell's row is : " & BottomRightRow & _
        ", Bottom Right cell's column is :" & BottomRightCol

End Sub