JimS
01-06-2012, 08:56 AM
In the attachment I have a small sample of the data that I need to sum.
I have removed a lot of the non essential code and data.
The actual data is about 100 rows deep but can get very wide.
I have some code that will hide the columns that are not required during the summing step, this code basically hides and unhides the columns based on the user’s selection criteria (code not included).
That all works fine.
I need to be able to total, by row, the visible cells and write the totals out in pre-determined cells.
I have a macro that does the summing and writes out the totals where I need them (see the SumData sub).
The trick is the data is made up of 2 columns (per entry), the data is in “pairs of factors” and I need to total for each of the individual factors.
The SumData sub does this but it is VERY slow as the data grows (up over 2 minutes).
There is a second sub in the attachment that will reference only the visible cells (see the CellsToSum sub).
Does anyone know if it is possible to combine the 2 Subs somehow so that the SumData code doesn’t have to look at each and every cell to determine if it’s visible?
I can’t figure out how to use the CellsToSum code to sum the correct “pairs” of data like the SumData code does.
Thanks for any helps and ideas…
JimS
I have removed a lot of the non essential code and data.
The actual data is about 100 rows deep but can get very wide.
I have some code that will hide the columns that are not required during the summing step, this code basically hides and unhides the columns based on the user’s selection criteria (code not included).
That all works fine.
I need to be able to total, by row, the visible cells and write the totals out in pre-determined cells.
I have a macro that does the summing and writes out the totals where I need them (see the SumData sub).
The trick is the data is made up of 2 columns (per entry), the data is in “pairs of factors” and I need to total for each of the individual factors.
The SumData sub does this but it is VERY slow as the data grows (up over 2 minutes).
There is a second sub in the attachment that will reference only the visible cells (see the CellsToSum sub).
Does anyone know if it is possible to combine the 2 Subs somehow so that the SumData code doesn’t have to look at each and every cell to determine if it’s visible?
I can’t figure out how to use the CellsToSum code to sum the correct “pairs” of data like the SumData code does.
Thanks for any helps and ideas…
JimS