如果没有什么超出您的数据，然后开始在最右边的行，然后向左走。即如果您的数据是在第1行，然后

.cells(1,columns.count).end(xltoleft)

请尝试下面的操作。仅当数据从A列开始时才能正确工作。对于行：

last_row = ActiveSheet.UsedRange.Rows.Count

对于色谱柱：

last_column = ActiveSheet.UsedRange.Columns.Count

您还可以使用：

last_row = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

最安全的方法是使用下面的公式，即使数据不是从A列开始，也会返回最后一行。

last_row = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

在这种情况下，最好使用CurrentRegion
因此，如果您的数据从单元格A1开始，您可以执行以下操作：Range("A1").CurrentRegion
当前标记的答案将为您提供工作表上所有数据的区域，这并不是真正的问题。

使用Find方法

• Range.Find Method

以下内容与使用Find方法获取（引用）行、列、给定范围或工作表中的NON-EMPTY范围有关。

基础知识

• 如果工作表被筛选，Find方法将失败。
• 如果工作表有隐藏的行或列，则Find方法仅在LookIn参数的参数设置为xlFormulas时才有效。
• 对于这些操作，Find方法的第4个和第7 - 9个参数（LookAt、MatchCase、MatchByte和SearchFormat）是不相关的。
• 相关的论据如下：
• 1.）What，始终为*
• 2.）After，默认为第一个单元格A1（仅 First，last cell），
• 3.）LookIn，总是xlFormulas也工作，如果'细胞'是隐藏的（不过滤！），并包括空白的非空细胞，例如=""，'，...，
• 5.）SearchOrder，（仅限 * 范围或工作表 *），
• 6.）SearchDirection，默认为xlNext（仅 Last，xlPrevious）。
  有趣
• 所有三个屏幕截图都是相同的，显示的范围为A1:L12，即已使用的范围（UsedRange）。请注意，尽管范围L1:L12和A12:K12不包含值，但它们（为空），也包括在内，因为它们的单元格之一不具有默认格式。使用UsedRange清空单元格。
  行透视（绿色）

Sub FindRow()
    
    Debug.Print "Row Perspective"
    
    Const SRC_ROW As Long = 6
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rg As Range: Set rg = ws.Rows(SRC_ROW)
    
    ' First (Left-Most) Non-Empty Cell
    Dim fCell As Range:
    Set fCell = rg.Find("*", rg.Cells(rg.Cells.Count), xlFormulas)
    If fCell Is Nothing Then
        Debug.Print "All cells are empty in row " & SRC_ROW & "!"
        Exit Sub
    Else
        Debug.Print "The first non-empty cell in row " & SRC_ROW _
            & " is cell """ & fCell.Address(0, 0) & """."
    End If

    ' Last (Right-Most) Non-Empty Cell
    Dim lCell As Range:
    Set lCell = rg.Find("*", , xlFormulas, , , xlPrevious)
    Debug.Print "The last non-empty cell in row " & SRC_ROW _
        & " is cell """ & lCell.Address(0, 0) & """."
    
    ' Non-Empty Range
    Dim nerg As Range: Set nerg = ws.Range(fCell, lCell)
    Debug.Print "The non-empty range in row " & SRC_ROW _
        & " is range """ & nerg.Address(0, 0) & """."

End Sub

结果：

Row Perspective
The first non-empty cell in row 6 is cell "F6".
The last non-empty cell in row 6 is cell "J6".
The non-empty range in row 6 is range "F6:J6".

柱透视（红色）

Sub FindColumn()
    
    Debug.Print "Column Perspective"
    
    Const SRC_COLUMN As String = "E"
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rg As Range: Set rg = ws.Columns(SRC_COLUMN)
    
    ' First (Top-Most) Non-Empty Cell
    Dim fCell As Range:
    Set fCell = rg.Find("*", rg.Cells(rg.Cells.Count), xlFormulas)
    If fCell Is Nothing Then
        Debug.Print "All cells are empty in column " & SRC_COLUMN & "!"
        Exit Sub
    Else
        Debug.Print "The first non-empty cell in column " & SRC_COLUMN _
            & " is cell """ & fCell.Address(0, 0) & """."
    End If

    ' Last (Bottom-Most) Non-Empty Cell
    Dim lCell As Range:
    Set lCell = rg.Find("*", , xlFormulas, , , xlPrevious)
    Debug.Print "The last non-empty cell in column " & SRC_COLUMN _
        & " is cell """ & lCell.Address(0, 0) & """."
    
    ' Non-Empty Range
    Dim nerg As Range: Set nerg = ws.Range(fCell, lCell)
    Debug.Print "The non-empty range in column " & SRC_COLUMN _
        & " is range """ & nerg.Address(0, 0) & """."

End Sub

结果：

Column Perspective
The first non-empty cell in column E is cell "E3".
The last non-empty cell in column E is cell "E10".
The non-empty range in column E is range "E3:E10".

范围和工作表透视图（黄色、蓝色）

Sub FindRange()
    
    Debug.Print "Range and Worksheet Perspective"
    
    Const SRC_RANGE As String = "A1:G8"
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim rg As Range: Set rg = ws.Range(SRC_RANGE) ' ws.UsedRange ' ws.Cells '
    ' Left-Most Non-Empty Cell
    Dim lfCell As Range:
    Set lfCell = _
        rg.Find("*", rg.Cells(rg.Cells.CountLarge), xlFormulas, , xlByColumns)
    If lfCell Is Nothing Then
        Debug.Print "All cells are empty in range """ & rg.Address(0, 0) & """!"
        Exit Sub
    Else
        Debug.Print "The left-most non-empty cell in range """ _
            & rg.Address(0, 0) & """ is cell """ & lfCell.Address(0, 0) & """."
    End If
    
    ' Top-Most Non-Empty Cell
    Dim tfCell As Range:
    Set tfCell _
        = rg.Find("*", rg.Cells(rg.Cells.CountLarge), xlFormulas, , xlByRows)
    Debug.Print "The top-most non-empty cell in range """ _
        & rg.Address(0, 0) & """ is cell """ & tfCell.Address(0, 0) & """."

    ' Right-Most Non-Empty Cell
    Dim rlCell As Range:
    Set rlCell = rg.Find("*", , xlFormulas, , xlByColumns, xlPrevious)
    Debug.Print "The right-most non-empty cell in range """ _
        & rg.Address(0, 0) & """ is cell """ & rlCell.Address(0, 0) & """."

    ' Bottom-Most Non-Empty Cell
    Dim blCell As Range:
    Set blCell = rg.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    Debug.Print "The bottom-most non-empty cell in range """ _
        & rg.Address(0, 0) & """ is cell """ & blCell.Address(0, 0) & """."
    
    ' First Non-Empty Cell
    Dim fCell As Range: Set fCell = ws.Cells(tfCell.Row, lfCell.Column)
    Debug.Print "The first cell of the non-empty range of range """ _
        & rg.Address(0, 0) & """ is cell """ & fCell.Address(0, 0) & """."
    
    ' Last Non-Empty Cell
    Dim lCell As Range: Set lCell = ws.Cells(blCell.Row, rlCell.Column)
    Debug.Print "The last cell of the non-empty range of range """ _
        & rg.Address(0, 0) & """ is cell """ & lCell.Address(0, 0) & """."

    ' Non-Empty Range
    Dim nerg As Range: Set nerg = ws.Range(fCell, lCell)
    Debug.Print "The non-empty range of range """ _
        & rg.Address(0, 0) & """ is range """ & nerg.Address(0, 0) & """."

End Sub

结果：

Range and Worksheet Perspective (Range)
The left-most non-empty cell in range "A1:G8" is cell "B4".
The top-most non-empty cell in range "A1:G8" is cell "D2".
The right-most non-empty cell in range "A1:G8" is cell "G5".
The bottom-most non-empty cell in range "A1:G8" is cell "C7".
The first cell of the non-empty range of range "A1:G8" is cell "B2".
The last cell of the non-empty range of range "A1:G8" is cell "G7".
The non-empty range of range "A1:G8" is range "B2:G7".

Range and Worksheet Perspective (Used Range)
The left-most non-empty cell in range "A1:L12" is cell "A9".
The top-most non-empty cell in range "A1:L12" is cell "I1".
The right-most non-empty cell in range "A1:L12" is cell "K5".
The bottom-most non-empty cell in range "A1:L12" is cell "D11".
The first cell of the non-empty range of range "A1:L12" is cell "A1".
The last cell of the non-empty range of range "A1:L12" is cell "K11".
The non-empty range of range "A1:L12" is range "A1:K11".

Range and Worksheet Perspective (Cells)
The left-most non-empty cell in range "1:1048576" is cell "A9".
The top-most non-empty cell in range "1:1048576" is cell "I1".
The right-most non-empty cell in range "1:1048576" is cell "K5".
The bottom-most non-empty cell in range "1:1048576" is cell "D11".
The first cell of the non-empty range of range "1:1048576" is cell "A1".
The last cell of the non-empty range of range "1:1048576" is cell "K11".
The non-empty range of range "1:1048576" is range "A1:K11".