Get last row in range that contains formulas

Hello everyone
I have range ("A2:A100") that contains formulas and the result of this formula sometimes is 0 ..
How can I get the last row that is greater than 0 ...?
I can do that using loops but I am searching for alternative approach using UDF for example or any other way avoiding loops

Thanks advanced for help

i can post a formula but here is a good tutorial:

http://xldynamic.com/source/xld.LastValue.html


look for formulas starting with =LOOKUP(2,1/...

Thank you very much for reply ..
I need UDF or vba code for that purpose as it is part of another code

Code:

---

Option Explicit

Public Function LastRowGreaterThanZero(ShtName As String, ColumnLetter As String) As Long
Dim Cel As Range

Set Cel = Sheets(ShtName).Cells(Rows.Count, ColumnLetter).End(xlUp)
Do While Cel.Value <= 0 Or Not IsNumeric(Cel)
Set Cel = Cel.Offset(-1)
Loop

LastRowGreaterThanZero = Cel.Row
End Function

---

Code:

---

Sub test_LastRowGreaterThanZero()
Dim x
x = LastRowGreaterThanZero("Sheet1", "A")
x = LastRowGreaterThanZero("Sheet1", "A:A")
End Sub

---

That's great Mr. Sam ..
It seems that there is no escape from loop .. I wish I find a similar way to that code

Code:

---

Sub LastNonEmptyRow()
    Dim wsData      As Worksheet
    Dim lrwsData    As Long


    Set wsData = Sheets("Data")
    lrwsData = wsData.Range("D:D").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    MsgBox lrwsData
End Sub

---

Which doesn't depend on loops ..

what?did you run the loop on an empty column?

Code:

---

Do While Cel.Value <= 0 Or Not IsNumeric(Cel) Or Cel.Row <> 1

---

Quote:

---

Which doesn't depend on loops .

---

Ain't gonna happen

The column has formulas till 12000 rows .. I know it is bad but I am developing it to get rid of those formulas ...

You marked the thread "Solved."

Are you happy with the solution offered?

Quote:

---

The column has formulas till 12000 rows .. I know it is bad but I am developing it to get rid of those formulas ...

---

If you want to get rid of all formulas that have results not zero. then
For each Cel in Column
If Cel.Value <> 0 then Cel.Value = Cel.Value
Will replace those formulas with the result.

Thanks a lot for great help. Yes I have used the UDF that you provided and it is very good
Thank you very much Mr. SamT
Best Regards

Quote:

---

Originally Posted by YasserKhalil

I need UDF

---

you don't need udf to return the row number of the last + non-zero cell in range A1:A100.

PHP Code:

---

=LOOKUP(2,1/(A1:A100>0),ROW(A1:A100)-ROW(A1)+1) 


---

Thanks a lot for sharing us the issue ..
I already searched and found solutions by formulas but didn't find UDF for that purpose so I posted this thread. Thanks anyway for your great contribution
Best Regards

You shouldn't use a UDF but macro instead.

Code:

---

Sub M_snb()
    [D1:D12500] = [D1:D12500].Value
End Sub

---

Thanks snb
This code will convert the range to value .. and the UDF required to get the last row in the range (which has formulas) .. Or I missed something !!?