在包含公式的单元格区域中查找最后一个非空行 [英] Find the last not empty row in a range of cells holding a formula

问题描述

如何在包含公式的单元格区域中找到最后一行，其中公式的结果是实际值而不是空?

How can I find the last row in a range of cells that hold a formula, where the result of the formula is an actual value and not empty?

以简化的方式说，单元格("E1:E10")的范围包含一个引用单元格A1到A10的公式，其后跟=IF("A1"="","","A1").但是只有单元格A1到A6填写了一个值，因此单元格E7到E10的公式结果将为空.

Say in a simplified way that the range of cells ("E1:E10") hold a formula referring to cells A1 through A10 as followed =IF("A1"="","","A1"). But only the cells A1 through A6 have a value filled in, so the result of the formula for cells E7 through E10 will be empty.

尝试使用:

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

生成值为10的lastRow.我想要的是lastRow的值在此示例中为6.

results in lastRow having the value of 10. What I want is for the value of lastRow to be 6 in this example.

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

The actual code is way more complex than this so I can't just check for the last filled in Row of Column A, as the formulas refer to single cells on different sheets and are added dynamically.

推荐答案

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

I think that more elegant way than was provided by @D_Bester is to use find() option without looping through the range of cells:

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

这篇关于在包含公式的单元格区域中查找最后一个非空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！