Get last row in range that contains formulas

[YasserKhalil]

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

[mancubus]

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/...

[YasserKhalil]

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

[SamT]

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




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

[YasserKhalil]

That's great Mr. Sam ..
It seems that there is no escape from loop .. I wish I find a similar way to that 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 ..

[SamT]

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


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



Which doesn't depend on loops .Ain't gonna happen

[YasserKhalil]

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

[SamT]

You marked the thread "Solved."

Are you happy with the solution offered?


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.

[YasserKhalil]

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

[mancubus]

I need UDF

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


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

[YasserKhalil]

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

[snb]

You shouldn't use a UDF but macro instead.


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

[YasserKhalil]

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 !!?