VBA LastRow计算不行 [英] VBA LastRow calculation not working

问题描述

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

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

 code> LastRow = Activesheet.Cells.Find（*，SearchOrder：= xlByRows，SearchDirection：= xlPrevious）.Row 
  

但是，有时候这会返回一个值，即使我有数千行的数据。只有当设置了过滤器（但是仍然有可见的行数据在其中）时，似乎只做到这一点，但即使这样，它并不总是发生，我看不到它的模式。

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

有谁知道为什么会发生这种情况？你有没有想过使用Greg的答案，但是循环查找所有列的最高位置？如下：

  LastRow = 1 
 With ActiveSheet 
对于i = 1到.UsedRange.Columns。计数
如果.Cells（.Rows.Count，i）.End（xlUp）.Row> LastRow然后
 LastRow = .Cells（.Rows.Count，i）.End（xlUp）.Row 
 EndIf 
下一个i 
结束
  

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

如果您设置为使用.Find尝试查看After：= [A1]参数。我没有探讨这个功能的特质，但这将是我从这个问题开始的地方。

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

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 .End method on a single column):

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.

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

Does anyone know why this would be happening?

解决方案

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

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.

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.

这篇关于VBA LastRow计算不行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！