查找包含公式的单元格范围中最后一个非空行

Question

如何查找包含公式的单元格范围中最后一个实际值而非空值的行？

简单来说，假设单元格范围为("E1:E10")，其公式引用了A1到A10这些单元格，例如=IF("A1"="","","A1")。但是只有A1到A6单元格中填写了值，因此E7到E10单元格的公式结果为空。

尝试使用以下方法来解决：

lastRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row

运行结果导致lastRow的值为10。在这个例子中，我希望lastRow的值是6。

实际代码比这个复杂得多，所以我不能只检查列A的最后一个填充的行，因为公式引用不同工作表上的单个单元格，并且是动态添加的。

- Pascale

Answer 1

我认为比@D_Bester提供的更加优雅的方式是使用find()选项，而不是循环遍历单元格范围：

Sub test()
    Dim cl As Range, i&
    Set cl = Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    i = cl.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Debug.Print "Last row with data: " & i
End Sub

测试

此外，提供的代码还有更短的版本：

Sub test2()
    Debug.Print [E:E].Find("*", , xlValues, , xlByRows, xlPrevious).Row
End Sub

- Vasily

---

工作得很好，而且更符合我的要求。 我只有一个问题，变量i声明中的&符号是什么意思？（因为似乎不加&也能正常工作） - Pascale

@Pascale，它的意思是Dim i as Long，如果你去掉&，那么i将被声明为Variant。这只是一种编码的简写方法，例如％是Integer，＆是Long，$是String，@是Currency，！是Single，＃是Double，如果没有指定，则为Variant。 - Vasily

@Vasily，感谢您给我提供了整个简写声明列表，对于微软没有在他们的变量声明页面中包含它们，我要打-1。 - Pascale

这个答案非常优美！ Россия很美丽。 - D_Bester

Answer 2

您想找到一列中不为空并且不是空字符串（""）的最后一个单元格。

只需使用“LastRow”并添加循环以检查非空单元格即可。

lastrow = ActiveSheet.Range("E" & ActiveSheet.Rows.Count).End(xlUp).Row
Do
    If ActiveSheet.Cells(lastrow, 5).Value <> "" Then
        Exit Do
    End If
    lastrow = lastrow - 1
Loop While lastrow > 0

If lastrow > 0 Then
    Debug.Print "Last row with data: " & lastrow
Else
    Debug.Print "No data in the column"
End If

请注意，您的 Rows.count 没有指定是哪个工作表。这意味着它将使用活动工作表。当然，ActiveSheet.Range() 也在活动工作表上。但是混合使用 Range 或 Rows 和 .Range 或 .Rows 是一个不好的做法。它表明了一种粗心的用法，如果更改了 ActiveSheet 但没有更改未指定的引用，则可能会出现问题。

- D_Bester

---

我已经考虑过像你建议的那样反向循环它们，但希望有一种更优雅的方法来实现它。 - Pascale

谢谢您的回答，也感谢您解释如何改进使用Rows.Count和ActiveSheet。 - Pascale

我认为可以使用“分而治之”的方法。因为2^20等于1048576，这就是Rows.Count的值。也就是说，你的Do...Loop循环不会超过20次就能到达最后一行。 - Rosetta

@D_Bester 还有一种选择是使用 find() 选项来查找最后一个非空单元格，无需通过范围循环。 - Vasily

Answer 3

这应该可以帮助您确定包含公式的最后一行（在 Sheet1 表的 A 列中）：

lastRow  = Split(Split(Sheet1.Range("A:A").SpecialCells(xlCellTypeFormulas).Address, ",")(UBound(Split(Sheet1.Range("A:A").SpecialCells(xlCellTypeFormulas).Address, ","))), "$")(2)

SpecialCells 用于确定包含公式的所有单元格的范围。然后使用 Split 解析此范围。使用 Ubound 获取这些单元格中的最后一个。结果再次拆分以提取行号。

- Ralph

---

我尝试应用您的代码，但它返回了第一行中有公式的结果。（我的表格范围是从第3行到第26行，而您的lastRow返回3：当我用MsgBox打印时，我期望它是21）不确定我需要调整什么，甚至不确定这是否是一个好方法。您说“这应该帮助您确定包含公式的最后一行”，但我需要包含公式的最后一行，其中该公式的结果不是“”（空字符串）。如果我误解了您的答案，对不起，因为我根本没有使用vba的经验，所以我完全依赖于在stackoverflow上找到的内容。 - Pascale

@Ralph Address属性被限制为255个字符长度的字符串，因此可能无法包括所有包含公式的区域。 - shg

Answer 4

Function returnLastRow()
    With ActiveSheet.UsedRange
    returnLastRow = .Find("*", , xlValues, , xlByRows, xlPrevious).Row
    End With
End Function

- OzDrooper