excel - 在 VBA 中查找选定区域的最后一行和最后一列

Question

我试图找到我在工作表中选择的区域的最后一行和最后一列。

通常我用它来查找最后一行和最后一列：

LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column

但是，这些行适用于 Row = 1 和 Column = 1。

假设我在其他地方有一张桌子。我想选择表格并运行代码。然后我想要以下变量：

FirstRowTable = 
LastRowTable =   
FirstColumnTable = 
LastColumnTable =

我尝试过这样的事情，但它不起作用：

LastRowTable = Selection.Row + Selection.Rows.Count - 1
FirstRowTable = Selection.Row

有任何想法吗？

标签： excelvba

Answer 1

以下是Selected矩形范围的一些参数：

Sub range_reporter()
    Dim r As Range
    Dim s As String
    Dim nLastRow As Long, nLastColumn As Long
    Dim nFirstRow As Long, nFirstColumn As Long

    Set r = Selection

    nLastRow = r.Rows.Count + r.Row - 1
    MsgBox ("last row " & nLastRow)

    nLastColumn = r.Columns.Count + r.Column - 1
    MsgBox ("last column " & nLastColumn)

    nFirstRow = r.Row
    MsgBox ("first row " & nFirstRow)

    nFirstColumn = r.Column
    MsgBox ("first column " & nFirstColumn)

    numrow = r.Rows.Count
    MsgBox ("number of rows " & numrow)

    numcol = r.Columns.Count
    MsgBox ("number of columns " & numcol)

    s = r.Address
    MsgBox ("address " & s)

    s = r(1).Address
    MsgBox ("address of first cell " & s)
    MsgBox ("worksheet " & r.Worksheet.Name)

    MsgBox ("workbook  " & r.Worksheet.Parent.Name)

    MsgBox ("item count " & r.Count)

    '
    'sub-ranges
    '
    MsgBox "address of first row " & r.Rows(1).Address
    MsgBox "address of first column " & r.Columns(1).Address
End Sub