VBA LastRow 计算不起作用

VBA LastRow calculation not working

提问by BBaxter

I have a worksheet with an autofiltered range that starts in cell B3. Column Acontains some macro buttons but is effectively blank. The top two rows contain information about the data in the main range.

我有一个从 cell 开始的自动过滤范围的工作表B3。列A包含一些宏按钮，但实际上是空白的。前两行包含有关主要范围内数据的信息。

In VBA, I am using what I believe is a standard method for determining the last row in a worksheet (In this case I cannot rely on the .Endmethod on a single column):

在 VBA 中，我使用我认为是确定工作表中最后一行的标准方法（在这种情况下，我不能依赖于.End单列的方法）：

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

However, sometimes this returns a value of one, even when I have thousands of rows of data. It seems to only do this when there are filters set (but there are still visible rows with data in them), but even then it doesn't always happen and I can't see a pattern to it.

但是，有时这会返回值 1，即使我有数千行数据。它似乎只在设置了过滤器时才这样做（但仍然有可见的包含数据的行），但即便如此，它也并不总是发生，我也看不到它的模式。

I know there are other solutions - I have changed to a UsedRangetechnique instead, but it is very frustrating that this particular one fails as it would otherwise be the most effective one in this situation.

我知道还有其他解决方案 - 我已经改用了一种UsedRange技术，但非常令人沮丧的是，这个特定的方法失败了，否则在这种情况下它会是最有效的方法。

Does anyone know why this would be happening?

有谁知道为什么会发生这种情况？

采纳答案by GetUserName

Have you thought of using Greg's answer, but looped to find the highest row of all the columns? Something like:

你有没有想过使用 Greg 的答案，但循环查找所有列的最高行？就像是：

LastRow = 1
With ActiveSheet
   For i = 1 to .UsedRange.Columns.Count
      If .Cells(.Rows.Count, i).End(xlUp).Row > LastRow Then
         LastRow = .Cells(.Rows.Count, i).End(xlUp).Row
      EndIf
   Next i
End With

This solution would allow for blank values randomly populated in bottom rows. UsedRange is tricky as it will return the furthest outlying row/column that has ever been edited (even if it is currently blank). In my experience Range.End(xlUp) behaves as you would expect if you pressed Ctrl-Up while in a worksheet. This is a little more predictable.

此解决方案将允许在底部行中随机填充空白值。UsedRange 很棘手，因为它会返回曾经编辑过的最远的外围行/列（即使它当前是空白的）。根据我的经验 Range.End(xlUp) 的行为与您在工作表中按 Ctrl-Up 时所期望的一样。这有点更可预测。

If you are set on using .Find try looking into the After:=[A1] argument. I haven't explored the idiosyncrasies of this function, but that would be the place I'd start given this problem.

如果您开始使用 .Find ，请尝试查看 After:=[A1] 参数。我还没有探索过这个函数的特性，但鉴于这个问题，那将是我开始的地方。

回答by Greg R

try this...

尝试这个...

Dim LastRow as long

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

This will get the last row in column A.

这将获得 A 列中的最后一行。

回答by brettdj

Suggest that you try using Findspecifying to lookin XlFormulasas unlike XlValues, hidden cells (but not filtered cells) will be detected with this argument.

建议您尝试使用Find指定来查找XlFormulas不同XlValues，隐藏单元格（但未过滤的单元格）将使用此参数检测。

Sub Test()
Dim rng1 As Range
Set rng1 = ActiveSheet.Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then MsgBox rng1.Row
End Sub

回答by user6651685

I faced the exact same issue this morning.

今天早上我遇到了完全相同的问题。

At first, I was convinced that ".find" feature is unstable.

起初，我确信“.find”功能不稳定。

But, after fiddling for a while, I found out a non-empty cell at a row number way too deep in my Sheet, think it was 1,000 or 10,000 or similar. I deleted it and ".find" works again. Probably, the limits of certain internal VBA variables are not big enough.

但是，在摆弄了一段时间后，我在工作表中太深的行号处发现了一个非空单元格，认为它是 1,000 或 10,000 或类似的。我删除了它，“.find”再次起作用。可能某些内部 VBA 变量的限制还不够大。

Do this:

做这个：

1) Press CTRL + END

1) 按 CTRL + END

2) Identify the non-empty cell(s), assuming this is inadvertently filled in, and delete it.

2) 确定非空单元格，假设这是无意中填写的，然后将其删除。

回答by Tom Annable

I know this is an old post but i have seen exactly this problem and not seen any answers that deal with the issue. It seems to occur sometimes on a dataset where there are rows hidden immediately after the last row. It doesn't matter whether you set to lookin xlformulas or xlvalues and i have tried every permutation of the find command i can find and it persistently returns the value 1. (As OP says) The solutions above don't fix this. I had to create a function which iterates to find the last row in this case (key bit of code below - in my case i needed to find lastrow in first two columns of various datasheets):

我知道这是一篇旧帖子，但我确实看到了这个问题，但没有看到任何解决该问题的答案。它似乎有时发生在数据集上，其中在最后一行之后立即隐藏了行。无论您设置为查找 xlformulas 还是 xlvalues 都没有关系，我已经尝试了我能找到的 find 命令的所有排列，并且它始终返回值 1。（正如 OP 所说）上面的解决方案不能解决这个问题。在这种情况下，我必须创建一个迭代以找到最后一行的函数（下面代码的关键位 - 在我的情况下，我需要在各种数据表的前两列中找到最后一行）：

On Error GoTo ExitLoop
StartRow = 1
LastRow = .Columns("A:B").Find(What:="*", SearchDirection:=xlNormal, LookIn:=xlValues, SearchOrder:=xlByRows).Row
StartRow = LastRow + 1
Do Until WorksheetFunction.CountA(.Range(.Cells(StartRow, 1), .Cells(1048576, 2))) = 0
    FindLastRow = .Range(.Cells(StartRow, 1), .Cells(1048576, 2)).Find(What:="*", SearchDirection:=xlNormal, LookIn:=xlValues, SearchOrder:=xlByRows).Row
    StartRow = LastRow + 1
Loop
ExitLoop:

回答by chipfall

My similar question was what's the last row and col used regardless of empty cells before with or without filtering. I cobbled this together from bits and pieces I could find and it does what I think both you and I want, at least for cells populated with data.

我的类似问题是最后一行和 col 使用的是什么，无论是否有过滤之前的空单元格。我从我能找到的点点滴滴中拼凑起来，它做我认为你和我想要的，至少对于填充有数据的单元格。

Function FindLastUsedRowAndCol(ByVal ws As Worksheet) As Variant()

Dim LastColRange As Range
Dim LastCol As Integer
Dim LastRow As Long
Dim LastRowTmp As Long
Dim RowXCol(2) As Variant


Set LastColRange = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
LastCol = LastColRange.Column

LastRow = 1

For i = 1 To LastCol Step 1
    If ws.FilterMode Then
        LastRow = ws.AutoFilter.Range.Rows.Count
        LastRowTmp = Cells(ws.Rows.Count, i).End(xlUp).row
        If LastRowTmp > LastRow Then LastRow = LastRowTmp
    Else
        LastRowTmp = Cells(ws.Rows.Count, i).End(xlUp).row
        If LastRowTmp > LastRow Then LastRow = LastRowTmp
    End If
Next i

RowXCol(1) = LastRow
RowXCol(2) = LastCol
FindLastUsedRowAndCol = RowXCol

End Function

And to test:

并测试：

Sub testit()
Dim ws As Worksheet
Set ws = Application.Worksheets("Sheet1")
cr = FindLastUsedRowAndCol(ws)
MsgBox "row: " & cr(1) & " col: " & cr(2)
End Sub

回答by chipfall

Try below code :

试试下面的代码：

Sub GetColA_LastRow()
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With

    MsgBox "The last row which has data in Col A of Sheet1 is " & lRow
End Sub

OR

或者

sub getLastRow()
 dim lastRow as long
 lastRow = Sheets("sheet1").Range("A65000").End(xlUp).Row

end sub

you may also visit the link for more details http://www.siddharthrout.com/2012/10/02/find-last-row-in-an-excel-sheetvbavb-net/

您也可以访问链接了解更多详情 http://www.siddharthrout.com/2012/10/02/find-last-row-in-an-excel-sheetvbavb-net/

Update the code after comments :

评论后更新代码：

Sub getLastRow()

    Dim rng As Range, lastRow As Long
    Set rng = Cells.Find("mango") ' here you enter whatever you want to find

    If Not rng Is Nothing Then
        lastRow = Sheets("sheet1").Cells(65000, rng.Column).End(xlUp).Row
    End If

End Sub

回答by Our Man in Bananas

how about:

怎么样：

with activesheet.usedrange
    LastRow = .rows.count
end with

hth Philip

菲利普