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