查找范围的最后一行 [英] Find last row in range

问题描述

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

I'm having a little trouble with finding the last row.

我想做的是在"A"列中找到最后一行，然后用它来查找范围内的最后一行.

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.

数据示例:

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

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

我正在使用列"A"中的最后一行因为从第29行开始的数据将始终具有相同的长度，所以在列"B"中使用的行将保持不变.第29行中的行数可以变化.

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"列中使用LR_wbSelect以获得我的最后一行，然后在LR_wbSelectNew中使用它作为起点进行查找.

So I am trying to use LR_wbSelect in column "A" to get my starting last Row, then within LR_wbSelectNew using it as the starting point to look up from.

当我将列设置为"A"，LR_wbSelectNew给我一行"17"时，此方法有效，但是当我将LR_wbSelectNew中的列更改为"B"时，此方法有效.它没有给出正确的最后一行"18".

This works when the column I set to "A", LR_wbSelectNew gives me the row of "17", but when I change the column in LR_wbSelectNew to "B" it doesn't give the correct last row of "18".

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

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.

在工作表上进行了一些测试之后，请按CRTL&从LR_wbSelect列的"B"的最后点向上.忽略行中的数据，然后转到找到数据的行.我看不到Excel认为这些单元格中没有数据的原因?

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

推荐答案

搜索LastRow(在B列中)有多种结果和方法.

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

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

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

使用时:

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

您要在CurrentRegion的B列中搜索包含数据的最后一行，该行从单元格B10开始，直到第一行没有数据(它停在第一行并为空行).

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).

完整代码:

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 :代码搜索包含值的单元格的最后一行(它会忽略内部包含公式的空白单元格.)

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

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

这篇关于查找范围的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！