PDA

View Full Version : [SOLVED] Get last row in range that contains formulas



YasserKhalil
07-24-2017, 10:59 AM
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
07-24-2017, 12:11 PM
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
07-24-2017, 12:19 PM
Thank you very much for reply ..
I need UDF or vba code for that purpose as it is part of another code

SamT
07-24-2017, 12:45 PM
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
07-24-2017, 01:14 PM
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
07-24-2017, 01:51 PM
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
07-24-2017, 02:14 PM
The column has formulas till 12000 rows .. I know it is bad but I am developing it to get rid of those formulas ...

SamT
07-24-2017, 02:31 PM
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
07-24-2017, 03:13 PM
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
07-24-2017, 11:23 PM
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
07-25-2017, 12:05 AM
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
07-25-2017, 05:46 AM
You shouldn't use a UDF but macro instead.


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

YasserKhalil
07-25-2017, 09:05 AM
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 !!?