VBA - How to find last used cell with values >=0 in a range with formulas?

[Bullstrik1]

Hi everyone!

I'v been searching this topic for quite some days now without sucess, so hopefully any of you could enlight me.

Lets say i have a range "A12:A5000" filled with formulas that return values between 0 and 100. Some of those fields are filled with "" if a certain condition is not met.

So, in vba, how can i determine witch row has value >=0?

Tkx in advance for your help

 

[Peter_SSs]

Try

Sub Last_Row()
  Dim lr As Long
  
  lr = Range("A12:A5000").Find(What:="*", After:=Range("A12"), LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  MsgBox lr
End Sub

 

[Rick Rothstein]

Try

Sub Last_Row()
  Dim lr As Long
  
  lr = Range("A12:A5000").Find(What:="*", After:=Range("A12"), LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  MsgBox lr
End Sub

Just to point out, if you run that code when no numbers are display in the range, an error is generated. Here is some different code that should also work (it displays 0 if none of the cells are displaying a number)...

Sub LastRow()
  Dim LastRow As Long
  LastRow = Evaluate("MAX(IF(LEN(A12:A5000),ROW(A12:A5000),0))")
End Sub

The above version is good to use if the range could change during execution and one wanted to concatenate the beginning and/or ending cell address into the string argument to the Evaluate function; however, if the range is always fixed at A12:A5000, then this slightly more compact macro can be used instead...

Sub LastRow()
  Dim LastRow As Long
  LastRow = [MAX(IF(LEN(A12:A5000),ROW(A12:A5000),0))]
End Sub

 

[Bullstrik1]

tkx both of you for the quick replys.
I will test those codes whenever i got the chance, and i'll post back my results.

cheers

 

[Peter_SSs]

Just to point out, if you run that code when no numbers are display in the range, an error is generated.

True. Would you believe me if I said I had considered that possibility but decided this meant "not all"?

Some of those fields are filled with ""

If it is possible that all cells in the range contain "" then the adjustment to my code would be:

Sub Last_Row()
  Dim lr As Long
  
  On Error Resume Next
  lr = Range("A12:A5000").Find(What:="*", After:=Range("A12"), LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  On Error GoTo 0
End Sub

If not re-evaluating last row multiple times, speed would not be an issue, but for me this (less compact) code is a good 10 times faster than the "evaluate" method so it would still be my choice.

 

[Bullstrik1]

Hi everyone!

I just tested the codes you gave me, and this one, works like a charm.

Sub Last_Row()  Dim lr As Long    On Error Resume Next  lr = Range("A12:A5000").Find(What:="*", After:=Range("A12"), LookIn:=xlValues, _    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row  On Error GoTo 0End Sub[/COLOR]

Thank you very much for your precious help
Cheers

 

[Rick Rothstein]

If it is possible that all cells in the range contain "" then the adjustment to my code would be:

Sub Last_Row()
  Dim lr As Long
  
  On Error Resume Next
  lr = Range("A12:A5000").Find(What:="*", After:=Range("A12"), LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  On Error GoTo 0
End Sub

If not re-evaluating last row multiple times, speed would not be an issue, but for me this (less compact) code is a good 10 times faster than the "evaluate" method so it would still be my choice.

There is one other possible problem users must keep in mind when using the code you posted (which is the approach I normally use for finding the last row, by the way)... if there are hidden rows, then your code will find the last row for the visible rows in Column A which many not be the last actual row of data. The Evaluate formula will return the last used row whether visible or not.

 

[Rick Rothstein]

There is one other possible problem users must keep in mind when using the code you posted (which is the approach I normally use for finding the last row, by the way)... if there are hidden rows, then your code will find the last row for the visible rows in Column A which many not be the last actual row of data (which, of course, may in fact be what the user wants). The Evaluate formula will return the last used row whether visible or not.

I meant to include the above in my last message.