Array Formulas and Last Cell

[Guest]

I have an array formula that extends 1500 rows. The formula in the cells
return data from another source. I need to graph this data, but the graph
(as expected) plots all the cells with no actual value.

How do I detect what row the last real value is contained in?

 

[Vasant Nanavati]

=MAX(ROW(A1:A1500)*(A1:A1500<>""))

entered as an array formula with <Ctrl> <Shift> <Enter>.

 

[Guest]

I need to do this in VBA. I do not have the ROW function available.

 

[Vasant Nanavati]

Simplified version:

Function LastRow() As Long
Dim i As Long
For i = 1500 to 1 Step -1
If Range(i, 1) <> "" Then
LastRow = i
Exit For
End If
Next
End Function

 

[Guest]

That works great, thankyou for your brain.

Simplified version:

Function LastRow() As Long
Dim i As Long
For i = 1500 to 1 Step -1
If Range(i, 1) <> "" Then
LastRow = i
Exit For
End If
Next
End Function