VBA 查找选区的角
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42220257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Finding corners of a selection
提问by Norsk
Given a rectangular Selection, how do I find the Cells for each corner? Specifically, the top right and bottom left.
给定一个矩形Selection，我如何找到Cell每个角的s？具体来说，右上角和左下角。
E.g. if a user selects the range B2:G9, I would like to get the address of each corner from the Selectionobject.
例如，如果用户选择 range B2:G9，我想从Selection对象中获取每个角的地址。
I used .Addressto 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.
我曾经.Address得到左上角和右下角，虽然我可以开始拆分字符串并对它们执行正则表达式替换，但我想知道是否有更清洁的方法。
回答by Shai Rado
Do you mean something like the code below.
你的意思是像下面的代码。
Note: it's better to stay away from Selectand Selection, you could try using With Range("B2:G9")instead (not implemented yet in the code below)
注意：最好远离Selectand 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
回答by Sivaprasath Vadivel
top_right_row=selection.rows(1).row
top_right_col=(selection.columns(1).column+selection.columns.count-1)
bottom_right_row=(selection.rows(1).row+selection.rows.count-1)
bottom_right_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
你可以得到这样的行和列值。我还没有测试代码，如果您有任何错误，请回复
