find the last row on a column with formula

[mabalo76]

The row 2 to row 30 of first column of sheet1 contain formula below

=IF(ISBLANK(sheet2!D2),"",sheet2!D2)
=IF(ISBLANK(sheet2!D3),"",sheet2!D3)
.
.
=IF(ISBLANK(sheet2!D30),"",sheet2!D30)


The thing is only row two actually have a value, from row 3 to row 30 is blank based on formula, but when i use below function
lRow = Cells(Rows.Count, 1).End(xlUp).Row

the lRow i got is 30

I want to get 2 in this case.

Anybody can help please?

 

[sadboy309]

You try:

Debug.Print Range("A2:A30").SpecialCells(xlCellTypeConstants).Row

 

[mabalo76]

It works, thank you very much.

 

[Marcelo Branco]

You try:

Debug.Print Range("A2:A30").SpecialCells(xlCellTypeConstants).Row

hmm... i don't think this returns the last row with data (not blank) in column A

Maybe...

lRow = ActiveSheet.Columns(1).Find(what:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row

M.

 

[mabalo76]

I just find out Debug.Print Range("A2:A30").SpecialCells(xlCellTypeConstants).Row didn't work

yours works fine.

Thanks,