如何查找特定列中包含数据的最后一行？

Question

我该如何找到特定工作表上特定列中最后一个包含数据的行？

- Lipis

---

更详细的回答可以在这里找到。 - Siddharth Rout

而且，就此而言，早期的帖子在这里： - brettdj

可能是在VBA中查找最后使用的单元格时出错的重复问题。 - M--

上面Siddharth和Masoud提到的VBA中查找最后一个使用的单元格时出现错误问题并不相同，尽管该问题的答案与本问题非常相关--我试图在这里总结了答案。brettdj提到的给定行中最后一个非空单元格（列）; Excel VBA问题是关于查找最后一列，其解决方案依赖于类似的思路，但需要不同的代码。 - Nickolay

Answer 1

如何呢：

Function GetLastRow(strSheet, strColumn) As Long
    Dim MyRange As Range

    Set MyRange = Worksheets(strSheet).Range(strColumn & "1")
    GetLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row
End Function

关于评论，即使最后一行只有一个单元格有数据，这将返回最后一个单元格的行号：

Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

- Remou

---

当满足以下条件时，此函数将返回错误的结果：

• strColumn是一个数字
• 第65536行存在数据
• 您使用带有超过65536行的Excel 2007。

- GSerg

@GSerg 如果您愿意，可以更正代码，然后删除评论（因为它将不再适用<g>） - JimmyPena

我认为如果单元格曾经有数据，但是后来数据被删除了，这段代码会错误地报告哪个单元格是最后一个。 - Scott Marcus

Siddharth Rout在“VBA中查找最后一个使用的单元格时出现错误”的答案中详细阐述了这两种方法，并指出了一些问题，例如使用Rows.Count（请参见“在列中查找最后一行”和“在工作表中查找最后一行”）; 我对该问题的回答列出了一些可能产生的意外后果（即，无法与自动筛选器一起使用以及.Find会影响Excel的查找对话框中的默认设置）。 - Nickolay

那个 Cells.Find 函数是我一直所需的！我厌倦了确定列数，我想要一个带有左/右上角和底部使用行范围的任何地方！ - FreeSoftwareServers

Answer 2

你应该使用.End(xlup)而不是使用65536，你可能想使用：

.End(xlUp).Row

sheetvar.Rows.Count

我相信这样做适用于Excel 2007，它有超过65536行的限制。

- Jon Fournier

---

这似乎是对@Fionnuala答案的评论。还要注意，xlUp从列末向后搜索，就像按下CTRL+UP键一样，因此在存在隐藏行的情况下（例如，启用自动筛选），它可能会产生不正确的结果。 - Nickolay

Answer 3

简单快速：

Dim lastRow as long
Range("A1").select
lastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

示例用法：

cells(lastRow,1)="Ultima Linha, Last Row. Youpi!!!!"

'or 

Range("A" & lastRow).Value = "FIM, THE END"

- user2988717

---

或者像这样：'函数 getSheetLastRow(sheet2Check as worksheet) lastRow = sheet2Check.Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).RowgetSheetLastRow=lastRow end function' - user2988717

这不仅限于特定的列，正如问题所要求的那样；它还使用了Select并访问了Range/Cells而没有指定工作表对象，这被认为是不好的风格。如果你觉得必须使用.Find，Siddharth Rout在“VBA中查找最后一个使用的单元格时出错”的答案中的“在工作表中查找最后一行”部分有更好的解决方案... - Nickolay

Answer 4

function LastRowIndex(byval w as worksheet, byval col as variant) as long
  dim r as range

  set r = application.intersect(w.usedrange, w.columns(col))
  if not r is nothing then
    set r = r.cells(r.cells.count)

    if isempty(r.value) then
      LastRowIndex = r.end(xlup).row
    else
      LastRowIndex = r.row
    end if
  end if
end function

使用方法：

? LastRowIndex(ActiveSheet, 5)
? LastRowIndex(ActiveSheet, "AI")

- GSerg

---

检查 isempty(r.value) 是否真的有必要？它不应该总是有一个值吗？ - gdelfino

@ gdelfino 是的。例如，列A在行1-10中有值，列B在行1-8中有值。 UsedRange 将是 A1:B10，与 B:B 的交集将是 B1:B10，最后一个单元格是 B10，它是空的。 - GSerg

为什么要增加额外的复杂度来获取UsedRange的最后一行，而不是直接从工作表的最后一行(sht.Rows.Count)开始，如果你已经使用了.end(xlup)呢？我认为，如果UsedRange不从第一行开始（即第一行为空），那么你获取最后一行的方式将会失败。 - Nickolay

@Nickolay 因为从工作表的最后一行开始是基于错误的假设，即工作表的最后一行为空。它通常是空的，但并不总是如此。我对这样的错误假设感到不满意。只有当列完全为空时，它才会跳过原始的“UsedRange”，这是正确的行为，因为唯一定义列表的两个变量是工作表和列号，这正是OP描述问题的方式。 - GSerg

不，这是支持if isempty(r.value)检查的论点 - 我赞同这一点。你可以从最后一行开始，仍然进行这个检查，但在此之前可以节省5行（同时删除错误的计数逻辑） - 并且最终得到一个更健壮的“查找列中最后一行”的版本，参见Siddharth Rout对“VBA中查找最后一个使用单元格时出错”的回答（仍未处理自动筛选，但对于某些用例来说这是可以接受的）。除非这是一种优化，我认为这并不必要，但我想先问一下。 - Nickolay

Answer 5

Public Function LastData(rCol As Range) As Range    
    Set LastData = rCol.Find("*", rCol.Cells(1), , , , xlPrevious)    
End Function

使用方法：?lastdata(activecell.EntireColumn).Address

- dkusleika

Answer 6

所有依赖于内置行为（如 .Find 和 .End ）的解决方案都有一些未经充分记录的限制（详见我的其他答案）。 我需要的是：

• 查找特定列中的最后一个非空单元格（即具有任何公式或值，即使它是空字符串）
• 依赖于具有明确定义行为的基元
• 可靠地处理自动筛选和用户修改
• 在10,000行上尽可能快地运行（在 Worksheet_Change 处理程序中运行而不感到缓慢）
• ...性能不会因意外数据或格式放在工作表末尾（大约1M行）而急剧下降

以下是解决方案：

• 使用 UsedRange 查找行号的上限（以便在常见情况下，即靠近使用范围的末尾时，可以快速搜索真正的“最后一行”）；
• 向后查找给定列中具有数据的行；
• ...使用VBA数组避免逐个访问每一行（如果在 UsedRange 中有许多行需要跳过）

（没有测试，抱歉）

' Returns the 1-based row number of the last row having a non-empty value in the given column (0 if the whole column is empty)
Private Function getLastNonblankRowInColumn(ws As Worksheet, colNo As Integer) As Long
    ' Force Excel to recalculate the "last cell" (the one you land on after CTRL+END) / "used range"
    ' and get the index of the row containing the "last cell". This is reasonably fast (~1 ms/10000 rows of a used range)
    Dim lastRow As Long: lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row - 1 ' 0-based

    ' Since the "last cell" is not necessarily the one we're looking for (it may be in a different column, have some
    ' formatting applied but no value, etc), we loop backward from the last row towards the top of the sheet).
    Dim wholeRng As Range: Set wholeRng = ws.Columns(colNo)

    ' Since accessing cells one by one is slower than reading a block of cells into a VBA array and looping through the array,
    ' we process in chunks of increasing size, starting with 1 cell and doubling the size on each iteration, until MAX_CHUNK_SIZE is reached.
    ' In pathological cases where Excel thinks all the ~1M rows are in the used range, this will take around 100ms.
    ' Yet in a normal case where one of the few last rows contains the cell we're looking for, we don't read too many cells.
    Const MAX_CHUNK_SIZE = 2 ^ 10 ' (using large chunks gives no performance advantage, but uses more memory)
    Dim chunkSize As Long: chunkSize = 1
    Dim startOffset As Long: startOffset = lastRow + 1 ' 0-based
    Do ' Loop invariant: startOffset>=0 and all rows after startOffset are blank (i.e. wholeRng.Rows(i+1) for i>=startOffset)
        startOffset = IIf(startOffset - chunkSize >= 0, startOffset - chunkSize, 0)
        ' Fill `vals(1 To chunkSize, 1 To 1)` with column's rows indexed `[startOffset+1 .. startOffset+chunkSize]` (1-based, inclusive)
        Dim chunkRng As Range: Set chunkRng = wholeRng.Resize(chunkSize).Offset(startOffset)
        Dim vals() As Variant
        If chunkSize > 1 Then
            vals = chunkRng.Value2
        Else ' reading a 1-cell range requires special handling <http://www.cpearson.com/excel/ArraysAndRanges.aspx>
            ReDim vals(1 To 1, 1 To 1)
            vals(1, 1) = chunkRng.Value2
        End If

        Dim i As Long
        For i = UBound(vals, 1) To LBound(vals, 1) Step -1
            If Not IsEmpty(vals(i, 1)) Then
                getLastNonblankRowInColumn = startOffset + i
                Exit Function
            End If
        Next i

        If chunkSize < MAX_CHUNK_SIZE Then chunkSize = chunkSize * 2
    Loop While startOffset > 0

    getLastNonblankRowInColumn = 0
End Function

- Nickolay

---

这种技术永远不会给出错误的答案，是其中最好的一种！我个人更喜欢使用XML的方式。 - MacroMarc

Answer 7

以下是找到最后一行、最后一列或最后一个单元格的解决方案。它解决了在查找所需列时 A1 R1C1 引用样式的问题。很遗憾我无法找到/记得代码来源，因此谢谢曾经在某个地方发布原始代码的人！

Sub Macro1
    Sheets("Sheet1").Select
    MsgBox "The last row found is: " & Last(1, ActiveSheet.Cells)
    MsgBox "The last column (R1C1) found is: " & Last(2, ActiveSheet.Cells)
    MsgBox "The last cell found is: " & Last(3, ActiveSheet.Cells)
    MsgBox "The last column (A1) found is: " & Last(4, ActiveSheet.Cells)
End Sub

Function Last(choice As Integer, rng As Range)
' 1 = last row
' 2 = last column (R1C1)
' 3 = last cell
' 4 = last column (A1)
    Dim lrw As Long
    Dim lcol As Integer

    Select Case choice
    Case 1:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       LookAt:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        Last = Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0
    Case 4:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0
        Last = R1C1converter("R1C" & Last, 1)
        For i = 1 To Len(Last)
            s = Mid(Last, i, 1)
            If Not s Like "#" Then s1 = s1 & s
        Next i
        Last = s1

    End Select

End Function

Function R1C1converter(Address As String, Optional R1C1_output As Integer, Optional RefCell As Range) As String
    'Converts input address to either A1 or R1C1 style reference relative to RefCell
    'If R1C1_output is xlR1C1, then result is R1C1 style reference.
    'If R1C1_output is xlA1 (or missing), then return A1 style reference.
    'If RefCell is missing, then the address is relative to the active cell
    'If there is an error in conversion, the function returns the input Address string
    Dim x As Variant
    If RefCell Is Nothing Then Set RefCell = ActiveCell
    If R1C1_output = xlR1C1 Then
        x = Application.ConvertFormula(Address, xlA1, xlR1C1, , RefCell) 'Convert A1 to R1C1
    Else
        x = Application.ConvertFormula(Address, xlR1C1, xlA1, , RefCell) 'Convert R1C1 to A1
    End If
    If IsError(x) Then
        R1C1converter = Address
    Else
        'If input address is A1 reference and A1 is requested output, then Application.ConvertFormula
        'surrounds the address in single quotes.
        If Right(x, 1) = "'" Then
            R1C1converter = Mid(x, 2, Len(x) - 2)
        Else
            x = Application.Substitute(x, "$", "")
            R1C1converter = x
        End If
    End If
End Function

- Greg Podesta

Answer 8

我想要添加一种更可靠的方法，使用UsedRange来找到最后一个已使用的行：

lastRow = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count - 1

同样地，要找到最后使用的列，您可以看这里

在立即窗口中的结果：

?Sheet1.UsedRange.Row+Sheet1.UsedRange.Rows.Count-1
 21 

- Stupid_Intern

---

请注意，UsedRange 还会选择公式，如果您将公式拖动到可见数据下方（或甚至格式），这可能会成为问题。 - micstr

@micstr 是的，但是其他所有方法也会这样做。 - Stupid_Intern

Answer 9

Last_Row = Range("A1").End(xlDown).Row

只是为了验证，假设您想要打印具有单元格C1中数据的最后一行的行号。

Range("C1").Select
Last_Row = Range("A1").End(xlDown).Row
ActiveCell.FormulaR1C1 = Last_Row

- Sumit Pokhrel

Answer 10

使用二分查找获取最后一个非空行

• 即使存在隐藏值，也会返回正确的值
• 如果在最后一个非空单元格之前存在空单元格（例如，第5行为空，但第10行是最后一个非空行），则可能返回不正确的值

Function getLastRow(col As String, ws As Worksheet) As Long
    Dim lastNonEmptyRow As Long
    lastNonEmptyRow = 1
    Dim lastEmptyRow As Long

    lastEmptyRow = ws.Rows.Count + 1
    Dim nextTestedRow As Long
    
    Do While (lastEmptyRow - lastNonEmptyRow > 1)
        nextTestedRow = Application.WorksheetFunction.Ceiling _
            (lastNonEmptyRow + (lastEmptyRow - lastNonEmptyRow) / 2, 1)
        If (IsEmpty(ws.Range(col & nextTestedRow))) Then
            lastEmptyRow = nextTestedRow
        Else
            lastNonEmptyRow = nextTestedRow
        End If
    Loop
    
    getLastRow = lastNonEmptyRow
    

End Function

- Potocpe1