Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining workbooks with some variable field names | Excel Discussion (Misc queries) | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
compile error: expected variable or function | Excel Discussion (Misc queries) | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions | |||
change function variable prompts?? | Excel Worksheet Functions |