# 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
```

test

**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 `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 `Split`

. With `Ubound`

the last of these cells is being retrieved. The result is being split again to extract the row number.