excel - VBA 查找选区的角点

Question

给定一个矩形选择，如何找到每个角的单元格？具体来说，是右上角和左下角。

例如如果用户选择范围 B2:G9，我想从 Selection 对象中获取每个角的地址。

我使用 .Address 来获取左上角和右下角，虽然我可以开始分割字符串并对它们执行正则表达式替换，但我想知道是否有更干净的方法。

Answer 1

你的意思是像下面的代码吗？

注意:最好远离 Select 和 Selection，您可以尝试使用 With Range("B2:G9 ") 相反(在下面的代码中尚未实现)

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