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

How can I find last row that contains data in a specific column?

提问by Lipis

How can I find the last row that contains data in a specific column and on a specific sheet?

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

采纳答案by Fionnuala

How about:

怎么样：

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

Regarding a comment, this will return the row number of the last cell even when only a single cell in the last row has data:

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

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

回答by Jon Fournier

You should use the .End(xlup)but instead of using 65536 you might want to use:

您应该使用.End(xlup)但您可能想要使用的 65536 而不是使用：

sheetvar.Rows.Count

That way it works for Excel 2007 which I believe has more than 65536 rows

这样它适用于 Excel 2007，我相信它有超过 65536 行

回答by user2988717

Simple and quick:

简单快捷：

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

Example use:

使用示例：

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

'or 

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

回答by GSerg

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

Usage:

用法：

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

回答by Nickolay

All the solutions relying on built-in behaviors (like .Findand .End) have limitations that are not well-documented (see my other answerfor details).

所有依赖于内置行为（如.Find和.End）的解决方案都有一些没有很好记录的限制（有关详细信息，请参阅我的其他答案）。

I needed something that:

我需要这样的东西：

• Finds the last non-emptycell (i.e. that has any formula or value, even if it's an empty string) in a specific column
• Relies on primitives with well-defined behavior
• Works reliably with autofilters and user modifications
• Runs as fast as possible on 10,000 rows (to be run in a Worksheet_Changehandler without feeling sluggish)
• ...with performance not falling off a cliff with accidental data or formatting put at the very end of the sheet (at ~1M rows)

• 查找特定列中的最后一个非空单元格（即具有任何公式或值，即使它是空字符串）
• 依赖于具有明确定义行为的原语
• 与自动过滤器和用户修改一起可靠地工作
• 在 10,000 行上尽可能快地运行（在Worksheet_Change处理程序中运行而不会感到迟钝）
• ...性能不会因为意外数据或格式放在工作表的最后（约 100 万行）而跌落悬崖

The solution below:

下面的解决方案：

• Uses UsedRangeto find the upper bound for the row number (to make the search for the true "last row" fast in the common case where it's close to the end of the used range);
• Goes backwards to find the row with data in the given column;
• ...using VBA arrays to avoid accessing each row individually (in case there are many rows in the UsedRangewe need to skip)

• 用于UsedRange查找行号的上限（在接近使用范围末尾的常见情况下，快速搜索真正的“最后一行”）；
• 向后查找在给定列中有数据的行；
• ...使用 VBA 数组避免单独访问每一行（以防UsedRange我们需要跳过许多行）

(No tests, sorry)

（没有测试，抱歉）

' 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

回答by Dick Kusleika

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

Usage: ?lastdata(activecell.EntireColumn).Address

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

回答by Dick Kusleika

Here's a solution for finding the last row, last column, or last cell. It addresses the A1 R1C1 Reference Style dilemma for the column it finds. Wish I could give credit, but can't find/remember where I got it from, so "Thanks!" to whoever it was that posted the original code somewhere out there.

这是查找最后一行、最后一列或最后一个单元格的解决方案。它解决了它找到的列的 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

回答by Stupid_Intern

I would like to add one more reliable way using UsedRangeto find the last used row:

我想添加一种更可靠的方法UsedRange来查找最后使用的行：

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

Similarly to find the last used column you can see this

类似地找到最后使用的列，你可以看到这个

Result in Immediate Window:

结果在立即窗口：

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

回答by Phaithoon Jariyanantakul

Public Function GetLastRow(ByVal SheetName As String) As Integer
    Dim sht As Worksheet
    Dim FirstUsedRow As Integer     'the first row of UsedRange
    Dim UsedRows As Integer         ' number of rows used

    Set sht = Sheets(SheetName)
    ''UsedRange.Rows.Count for the empty sheet is 1
    UsedRows = sht.UsedRange.Rows.Count
    FirstUsedRow = sht.UsedRange.Row
    GetLastRow = FirstUsedRow + UsedRows - 1

    Set sht = Nothing
End Function

sheet.UsedRange.Rows.Count: retrurn number of rows used, not include empty row above the first row used

sheet.UsedRange.Rows.Count：返回使用的行数，不包括使用的第一行上方的空行

if row 1 is empty, and the last used row is 10, UsedRange.Rows.Count will return 9, not 10.

如果第 1 行为空，并且最后使用的行是 10，UsedRange.Rows.Count 将返回 9，而不是 10。

This function calculate the first row number of UsedRange plus number of UsedRange rows.

此函数计算 UsedRange 的第一行数加上 UsedRange 行数。

回答by Sumit Pokhrel

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

Just to verify, let's say you want to print the row number of the last row with the data in cell C1.

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

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