Array Formulas and Last Cell
 

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?

Array Formulas and Last Cell
 

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

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

--

Vasant


"PosseJohn" wrote in message
...
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?

Array Formulas and Last Cell
 

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



"Vasant Nanavati" wrote:

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

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

--

Vasant


"PosseJohn" wrote in message
...
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?

Array Formulas and Last Cell
 

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

--

Vasant



"PosseJohn" wrote in message
...
I need to do this in VBA. I do not have the ROW function available.



"Vasant Nanavati" wrote:

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

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

--

Vasant


"PosseJohn" wrote in message
...
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?

Array Formulas and Last Cell
 

That works great, thankyou for your brain.

"Vasant Nanavati" wrote:

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

--

Vasant



"PosseJohn" wrote in message
...
I need to do this in VBA. I do not have the ROW function available.



"Vasant Nanavati" wrote:

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

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

--

Vasant


"PosseJohn" wrote in message
...
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?