with - Find the last not empty row in a range of cells holding a formula
last non empty cell in a column (2)
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?
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.
Trying to do it with:
lastRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
results in lastRow having the value of
10. What I want is for the value of lastRow to be
6 in this example.
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.
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
Also, more shorter version of the code which was provided above is:
Sub test2() Debug.Print [E:E].Find("*", , xlValues, , xlByRows, xlPrevious).Row End Sub
This should help you determine the last row containing a formula (in column
A on sheet1
lastRow = Split(Split(Sheet1.Range("A:A").SpecialCells(xlCellTypeFormulas).Address, ",")(UBound(Split(Sheet1.Range("A:A").SpecialCells(xlCellTypeFormulas).Address, ","))), "$")(2)
SpecialCells is used to determine the range of all the cells containing a formula. This range is then parsed using
Ubound the last of these cells is being retrieved. The result is being split again to extract the row number.