Excel VBA 查找范围内的最后一行

Excel VBA Find last row in range

提问by atame

im having a little trouble with finding the last row.

我在查找最后一行时遇到了一些麻烦。

What i am trying to do is find the last row in column "A", then use that to find the last row within a range.

我想要做的是找到“A”列中的最后一行，然后使用它来查找范围内的最后一行。

Example of Data:

数据示例：

 1) LR_wbSelect = wbshtSelect.cells(Rows.count, "A").End(xlUp).Row - 22

 2) LR_wbSelectNew = wbshtSelect.cells(LR_wbSelect, "A").End(xlUp).Row

I am using the last row in column "A" as the data from row 29 down will always be the same length, the rows used in column "B" from row 29 can be a varying number of rows.

我使用“A”列中的最后一行，因为第 29 行向下的数据将始终具有相同的长度，第 29 行“B”列中使用的行可以是不同的行数。

So i am trying to use LR_wbSelectin column "A" to get my starting last Row, then within LR_wbSelectNewusing it as the starting point to look up from.

所以我试图LR_wbSelect在“A”列中使用我的最后一行，然后LR_wbSelectNew将其用作查找的起点。

This works when the column i set to "A", LR_wbSelectNewgives me the row of "17", but when i change the column in LR_wbSelectNewto "B" it doesnt give the correct last row of "18".

当我设置为“A”LR_wbSelectNew的列给我“17”的行时，这有效，但是当我将列更改LR_wbSelectNew为“B”时，它没有给出正确的“18”的最后一行。

I can change the column to "C,D,E,F" and the code works fine, but the only column that i can use is "B" because it will always have data in it, where the rest of that row could have a blank cell.

我可以将列更改为“C、D、E、F”并且代码工作正常，但我可以使用的唯一列是“B”，因为它始终包含数据，该行的其余部分可能有一个空白单元格。

After doing some testing on the sheet, by pressing CRTL & Up from the lastring point of LR_wbSelectcolumn "B" ignores the data in the rows and go to the row where it find data. I can't see a reason why excel doesnt think there is data in these cells??

在工作表上进行一些测试后，通过从LR_wbSelect“B”列的最后一个点按 CRTL & Up忽略行中的数据并转到查找数据的行。我看不出为什么 excel 认为这些单元格中没有数据的原因？

Hopefully i have explained that so you can follow, if not please ask.

希望我已经解释了，以便您可以关注，如果没有，请询​​问。

If need be i can upload the full code if needed, just let me know.

如果需要，我可以上传完整的代码，如果需要，请告诉我。

Any help would be appreciated.

任何帮助，将不胜感激。

回答by Shai Rado

There are mulitple results and methods when searching for the LastRow (in Column B).

搜索 LastRow（在 B 列中）时有多种结果和方法。

When using Cells(.Rows.Count, "B").End(xlUp).Rowyou will get the last row with data in Column B (it ignores rows with spaces, and goes all the way down).

使用时，Cells(.Rows.Count, "B").End(xlUp).Row您将获得 B 列中包含数据的最后一行（它忽略带有空格的行，并一直向下）。

When using:

使用时：

 With wbshtSelect.Range("B10").CurrentRegion
     LR_wbSelectNew = .Rows(.Rows.Count).Row
 End With

You are searching for the last row with data in Column B of the CurrentRegion, that starts from cell B10, untill the first line without data (it stops on the first row with empty row).

您正在搜索CurrentRegion从单元格 B10 开始的 的B 列中包含数据的最后一行，直到没有数据的第一行（它在具有空行的第一行停止）。

Full Code:

完整代码：

Sub GetLastRow()

Dim wbshtSelect         As Worksheet
Dim LR_wbSelectNew      As Long

' modify "Sheet2" to your sheet's name
Set wbshtSelect = Sheets("Sheet2")

' find last row with data in Column B
With wbshtSelect
    LR_wbSelectNew = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
' for debug only
Debug.Print LR_wbSelectNew ' >>result 31

' find last row with data in Column B at current regioun starting at cell B10
With wbshtSelect.Range("B10").CurrentRegion
    LR_wbSelectNew = .Rows(.Rows.Count).Row
End With
' for debug only
Debug.Print LR_wbSelectNew ' >> result 18

End Sub

Edit1: code searches for last row for cells with values (it ignores blank cells with formulas inside).

Edit1：代码搜索具有值的单元格的最后一行（它忽略内部带有公式的空白单元格）。

Sub GetLastRow()

Dim wbshtSelect         As Worksheet
Dim LR_wbSelectNew      As Long

' modify "Sheet2" to your sheet's name
Set wbshtSelect = Sheets("Sheet2")

' find last row with data in Column B at current regioun starting at cell B10
With wbshtSelect.Range("B10").CurrentRegion
    LR_wbSelectNew = .Rows(.Rows.Count).Row
End With

Dim Rng         As Range    
Set Rng = wbshtSelect.Range("B10:B" & LR_wbSelectNew)

' find last row inside the range, ignore values inside formulas
LR_wbSelectNew = Rng.Find(What:="*", _
                    After:=Range("B10"), _
                    LookAt:=xlPart, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

' for debug
Debug.Print LR_wbSelectNew  ' << result 18 (with formulas in the range)

End Sub

回答by Rockstar

Hope this piece of code helps !

希望这段代码有帮助！

Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    MsgBox LastRow
End Sub

回答by Niclas

If your wbshtSelect is defined as worksheet and you have used set to define the specific worksheet, you can use this.

如果您的 wbshtSelect 被定义为工作表并且您已经使用 set 来定义特定的工作表，则可以使用它。

 Dim LastRow As Long

 wbshtSelect.UsedRange ' Refresh UsedRange
 LastRow = wbshtSelect.UsedRange.Rows(wbshtSelect.UsedRange.Rows.Count).Row

Otherwise take a look here http://www.ozgrid.com/VBA/ExcelRanges.htm

否则看看这里http://www.ozgrid.com/VBA/ExcelRanges.htm

回答by ArindamD

LR_wbSelectNew = wbshtSelect.cells(LR_wbSelect, "B").End(xlUp).Row

Why are you using "LR_wbSelect" as the row counter? If you want to know the last row of column 'B', you should use Rows.count

为什么使用“LR_wbSelect”作为行计数器？如果你想知道'B'列的最后一行，你应该使用Rows.count

Rows.count --> Returns maximum number of rows (which is 1048576 for Excel 2007 and up) End(xlUp) --> Moves the pointer upward to the last used row

Rows.count --> 返回最大行数（对于 Excel 2007 及更高版本为 1048576） End(xlUp) --> 将指针向上移动到最后使用的行

So, cells(Rows.count, "A").End(xlUp).Row --> This moves the pointer to the last row if the column 'A' (as if you are pressing Crtl+Up keys when A1048576 cell is selected)

因此，cells(Rows.count, "A").End(xlUp).Row --> 如果列 'A'，这会将指针移动到最后一行（就像当 A1048576 单元格为已选择）

So, use Rows.count to select the last row for column 'B' as well. If you have some specific requirement related to LR_wbSelect, please mention it.

因此，也使用 Rows.count 选择列 'B' 的最后一行。如果您有一些与 LR_wbSelect 相关的特定要求，请提及。

Alternatively, if you want to know the last row used in a sheet, you may use the below:

或者，如果您想知道工作表中使用的最后一行，您可以使用以下内容：

mySheet.Cells.SpecialCells(xlCellTypeLastCell).Row

回答by Marcucciboy2

I came here looking for a way to find the last row in a non-contiguousrange. Most responses here only check one column at a time so I created a few different functions to solve this problem. I will admit, though, that my .Find()implementation is essentially the same as Shai Rado's answer.

我来这里是为了寻找一种在非连续范围内找到最后一行的方法。这里的大多数回复一次只检查一列，所以我创建了几个不同的函数来解决这个问题。不过，我承认，我的.Find()实现与 Shai Rado 的回答基本相同。

Implementation 1 - Uses Range().Find()in reverse order

实现 1 -Range().Find()以相反的顺序使用

Function LastRowInRange_Find(ByVal rng As Range) As Long

    'searches range from bottom up stopping when it finds anything (*)
    Dim rngFind As Range
    Set rngFind = rng.Find( What:="*", _
                            After:=Cells(rng.row, rng.Column), _
                            LookAt:=xlWhole, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious)

    If Not rngFind Is Nothing Then
        LastRowInRange_Find = rngFind.row
    Else
        LastRowInRange_Find = rng.row
    End If

End Function

Implementation 2 - Uses Range().End(xlUp)on each column

实施 2 -Range().End(xlUp)在每列上使用

Function LastRowInRange_xlUp(ByVal rng As Range) As Long

    Dim lastRowCurrent As Long
    Dim lastRowBest As Long

    'loop through columns in range
    Dim i As Long
    For i = rng.Column To rng.Column + rng.Columns.count - 1
        If rng.Rows.count < Rows.count Then
            lastRowCurrent = Cells(rng.row + rng.Rows.count, i).End(xlUp).row
        Else
            lastRowCurrent = Cells(rng.Rows.count, i).End(xlUp).row
        End If

        If lastRowCurrent > lastRowBest Then
            lastRowBest = lastRowCurrent
        End If
    Next i

    If lastRowBest < rng.row Then
        LastRowInRange_xlUp = rng.row
    Else
        LastRowInRange_xlUp = lastRowBest
    End If

End Function

Implementation 3 - Loops through an Array in reverse order

实现 3 - 以相反的顺序循环遍历数组

Function LastRowInRange_Array(ByVal rng As Range) As Long

    'store range's data as an array
    Dim rngValues As Variant
    rngValues = rng.Value2

    Dim lastRow As Long

    Dim i As Long
    Dim j As Long

    'loop through range from left to right and from bottom upwards
    For i = LBound(rngValues, 2) To UBound(rngValues, 2)                'columns
        For j = UBound(rngValues, 1) To LBound(rngValues, 1) Step -1    'rows

            'if cell is not empty
            If Len(Trim(rngValues(j, i))) > 0 Then
                If j > lastRow Then lastRow = j

                Exit For
            End If

        Next j
    Next i

    If lastRow = 0 Then
        LastRowInRange_Array = rng.row
    Else
        LastRowInRange_Array = lastRow + rng.row - 1
    End If

End Function

I have not tested which of these implementations works fastest on large sets of data, but I would imagine that the winner would be _Arraysince it is not looping through each cell on the sheet individually but instead loops through the data stored in memory. However, I have included all 3 for variety :)

我还没有测试过这些实现中的哪一个在大数据集上运行最快，但我想获胜者会是，_Array因为它不是单独循环遍历工作表上的每个单元格，而是循环遍历存储在内存中的数据。但是，我已将所有 3 种都包括在内 :)

---

---

How to use

如何使用

To use these functions, you drop them into your code sheet/module, specify a range as their parameter, and then they will return the "lowest" filled row within that range.

要使用这些函数，请将它们放入代码表/模块中，指定一个范围作为它们的参数，然后它们将返回该范围内的“最低”填充行。

Here's how you can use any of them to solve the initial problem that was asked:

以下是如何使用它们中的任何一个来解决最初提出的问题：

Sub answer()

    Dim testRange As Range
    Set testRange = Range("A1:F28")

    MsgBox LastRowInRange_Find(testRange)
    MsgBox LastRowInRange_xlUp(testRange)
    MsgBox LastRowInRange_Array(testRange)

End Sub

Each of these will return 18.

这些中的每一个都会返回18。

回答by Punith GP

LR_wbSelect = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

回答by cssyphus

Shai Rado's first solution is a great one, but for some it might need a bit more elaboration:

Shai Rado 的第一个解决方案是一个很好的解决方案，但对于某些人来说可能需要更多的阐述：

 Dim rngCurr, lastRow
 rngCurr = wbshtSelect.Range("B10").CurrentRegion
 lastRow = rngCurr.Rows(rngCurr.Rows.Count).Row

If you want to know the last used row in the entire worksheet:

如果您想知道整个工作表中最后使用的行：

 Dim rngCurr, lastRow
 rngCurr = Range("A1").CurrentRegion
 lastRow = rngCurr.Rows(rngCurr.Rows.Count).Row

If you find this helpful, please upvote his answer.

如果您觉得这有帮助，请为他的回答点赞。

回答by cssyphus

Range().Endwill bring you to the end of a code block. If the starting cell is empty, it brings you the the first used cell or the last cell. It the cells is not empty it brings you to the last used cell. For this reason, you need to test whether or not the cell in column B is to determine whether to use LR_wbSelectNewas the last row.

Range().End将带您到代码块的末尾。如果起始单元格为空，它会为您提供第一个使用的单元格或最后一个单元格。如果单元格不是空的，它会将您带到上次使用的单元格。为此，您需要测试B列中的单元格是否确定是否LR_wbSelectNew用作最后一行。

With wbshtSelect
    LR_wbSelect = .Cells(Rows.Count, "A").End(xlUp).Row - 22

    If .Cells(LR_wbSelect, "B") <> "" Then
        LR_wbSelectNew = LR_wbSelect
    Else
        LR_wbSelectNew = .Cells(LR_wbSelect, "B").End(xlUp).Row
    End If
End With

This code defines a Target range that extends from A1 to the last row in column a - 22 and extends 10 columns.

此代码定义了一个目标范围，该范围从 A1 扩展到 a - 22 列中的最后一行并扩展了 10 列。

Dim Target As Range
With wbshtSelect
    Set Target = .Range("A1", .Cells(Rows.Count, "A").End(xlUp).Offset(-22)).Resize(, 10)
End With

回答by Jayant Kumar jain

    'This is sure method to find or catch last row in any column even   'if  some cell are blank in-between. (Excel-2007)` 
'This works even if sheet is not active

    'mycol is the column you want to get last row number

for n=1048575 to 1 step -1
myval=cells(n,mycol)
if myval<>"" then
mylastrow=n 'this is last row in the column
exit for
end if
next

ret=msgbox("Last row in column-" & mycol & "is=" & mylastrow)

回答by adofil

Dim rng As Range
Dim FirstRow, LastRow As long

Set rng = Selection

With rng

 FirstRow = ActiveCell.Row

 LastRow = .Rows(.Rows.Count).Row

End With