Array Formulas and Last Cell

[PosseJohn]

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

--

Vasant


"PosseJohn" <PosseJohn@discussions.microsoft.com> wrote in message
news:B03D390D-7CBE-43B4-805F-946503C313AA@microsoft.com...
> 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?

[PosseJohn]

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" <PosseJohn@discussions.microsoft.com> wrote in message
> news:B03D390D-7CBE-43B4-805F-946503C313AA@microsoft.com...
> > 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]

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" <PosseJohn@discussions.microsoft.com> wrote in message
news:44CF4427-56C5-49A4-A27A-0F28B6F8FC39@microsoft.com...
> 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" <PosseJohn@discussions.microsoft.com> wrote in message
> > news:B03D390D-7CBE-43B4-805F-946503C313AA@microsoft.com...
> > > 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?
> >
> >
> >

[PosseJohn]

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" <PosseJohn@discussions.microsoft.com> wrote in message
> news:44CF4427-56C5-49A4-A27A-0F28B6F8FC39@microsoft.com...
> > 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" <PosseJohn@discussions.microsoft.com> wrote in message
> > > news:B03D390D-7CBE-43B4-805F-946503C313AA@microsoft.com...
> > > > 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?
> > >
> > >
> > >
>
>
>