VBA Finding corners of a selection

    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


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

Tags:

Excel

Vba