Performing a function on a column of variable length
 

I am cutting and pasting rows of data from our ERP system into a spread
sheet. I then create a table at the end of the data calculating various
statistics. The column layout is fixed, so I always know which column I need
to perform the functions. The number of Row is NOT fixed, so I need a way to
indicate the end of the range.

Something like

=AVERAGE(RegionAround(B2,ColumnOnly))
where RegionAround(B2,ColumnOnly) would return B2:<row before first
blank cell

I'm willing to write a VBA function to return this information, but I can't
get the recieving function to accept the value back as a range.

I am open to all ideas of performing functions on variable length columns.

Thanks,

ER.

You could use this UDF:

Function EndCellTxt(a As Range) As String
EndCellTxt = a.End(xlDown).Address
End Function

Use with the INDIRECT function, like in

=SUM(B2:INDIRECT(endcelltxt(B2)))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"BeenThereGotLost" wrote in
message ...
I am cutting and pasting rows of data from our ERP system into a spread
sheet. I then create a table at the end of the data calculating various
statistics. The column layout is fixed, so I always know which column I
need
to perform the functions. The number of Row is NOT fixed, so I need a way
to
indicate the end of the range.

Something like

=AVERAGE(RegionAround(B2,ColumnOnly))
where RegionAround(B2,ColumnOnly) would return B2:<row before first
blank cell

I'm willing to write a VBA function to return this information, but I
can't
get the recieving function to accept the value back as a range.

I am open to all ideas of performing functions on variable length columns.

Thanks,

ER.

I think that in order to cope with lovalized versions and other reference
style the function should be

Function EndCellTxt(a As Range) As String
EndCellTxt = a.End(xlDown).AddressLocal(, , Application.ReferenceStyle)
End Function

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"Niek Otten" wrote in message
...
You could use this UDF:

Function EndCellTxt(a As Range) As String
EndCellTxt = a.End(xlDown).Address
End Function

Use with the INDIRECT function, like in

=SUM(B2:INDIRECT(endcelltxt(B2)))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"BeenThereGotLost" wrote in
message ...
I am cutting and pasting rows of data from our ERP system into a spread
sheet. I then create a table at the end of the data calculating various
statistics. The column layout is fixed, so I always know which column I
need
to perform the functions. The number of Row is NOT fixed, so I need a way
to
indicate the end of the range.

Something like

=AVERAGE(RegionAround(B2,ColumnOnly))
where RegionAround(B2,ColumnOnly) would return B2:<row before first
blank cell

I'm willing to write a VBA function to return this information, but I
can't
get the recieving function to accept the value back as a range.

I am open to all ideas of performing functions on variable length
columns.

Thanks,

ER.

Many Thanks, this is exactly what I needed. I did not realize you could use
the Indirect() function on the sides of the ":" in specifying the range.

ER.

"Niek Otten" wrote:

You could use this UDF:

Function EndCellTxt(a As Range) As String
EndCellTxt = a.End(xlDown).Address
End Function

Use with the INDIRECT function, like in

=SUM(B2:INDIRECT(endcelltxt(B2)))

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"BeenThereGotLost" wrote in
message ...
I am cutting and pasting rows of data from our ERP system into a spread
sheet. I then create a table at the end of the data calculating various
statistics. The column layout is fixed, so I always know which column I
need
to perform the functions. The number of Row is NOT fixed, so I need a way
to
indicate the end of the range.

Something like

=AVERAGE(RegionAround(B2,ColumnOnly))
where RegionAround(B2,ColumnOnly) would return B2:<row before first
blank cell

I'm willing to write a VBA function to return this information, but I
can't
get the recieving function to accept the value back as a range.

I am open to all ideas of performing functions on variable length columns.

Thanks,

ER.