Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Visible Cells Only
I have created a structured spreadsheet (not filterable) for reporting of
various sales by industry. I have also added macros which displays only rows related to specific industry groups. To ensure the totals sum only the visible cells I have added the attached function code to the spreadsheet which I sourced from the Microsoft resources and another site; Function Sum_Visible(Cells_To_Sum As Object) Application.Volatile For Each cell In Cells_To_Sum If cell.Rows.Hidden = False Then If cell.Columns.Hidden = False Then total = total + cell.Value End If End If Next Sum_Visible = total End Function The function works fine as long as the except that the total it provides is based on the cells referred to in the function but on the active worksheet. So for example if the function is used in sheet 2 and sheet 3 but application.calculate is run while in sheet 1, the functions sums the relevant cells selected on sheet1. The only other relevant thing is that the function is applied to a non-continguous range, if that makes any difference. I have created a workaround by forcing application.calculate each time a sheet is selected. It's a little sloppy but at least the function is working. Any assistance would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Visible Cells Only
you could use this formula, and even write code using it if you have excel 2003
=subtotal(109,a1:a10) -- Gary "Tiger" wrote in message ... I have created a structured spreadsheet (not filterable) for reporting of various sales by industry. I have also added macros which displays only rows related to specific industry groups. To ensure the totals sum only the visible cells I have added the attached function code to the spreadsheet which I sourced from the Microsoft resources and another site; Function Sum_Visible(Cells_To_Sum As Object) Application.Volatile For Each cell In Cells_To_Sum If cell.Rows.Hidden = False Then If cell.Columns.Hidden = False Then total = total + cell.Value End If End If Next Sum_Visible = total End Function The function works fine as long as the except that the total it provides is based on the cells referred to in the function but on the active worksheet. So for example if the function is used in sheet 2 and sheet 3 but application.calculate is run while in sheet 1, the functions sums the relevant cells selected on sheet1. The only other relevant thing is that the function is applied to a non-continguous range, if that makes any difference. I have created a workaround by forcing application.calculate each time a sheet is selected. It's a little sloppy but at least the function is working. Any assistance would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Visible Cells Only
Gary,
Thanks for the tip .... I guess it pays to look at the help more regularly. "Gary Keramidas" wrote: you could use this formula, and even write code using it if you have excel 2003 =subtotal(109,a1:a10) -- Gary "Tiger" wrote in message ... I have created a structured spreadsheet (not filterable) for reporting of various sales by industry. I have also added macros which displays only rows related to specific industry groups. To ensure the totals sum only the visible cells I have added the attached function code to the spreadsheet which I sourced from the Microsoft resources and another site; Function Sum_Visible(Cells_To_Sum As Object) Application.Volatile For Each cell In Cells_To_Sum If cell.Rows.Hidden = False Then If cell.Columns.Hidden = False Then total = total + cell.Value End If End If Next Sum_Visible = total End Function The function works fine as long as the except that the total it provides is based on the cells referred to in the function but on the active worksheet. So for example if the function is used in sheet 2 and sheet 3 but application.calculate is run while in sheet 1, the functions sums the relevant cells selected on sheet1. The only other relevant thing is that the function is applied to a non-continguous range, if that makes any difference. I have created a workaround by forcing application.calculate each time a sheet is selected. It's a little sloppy but at least the function is working. Any assistance would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Visible Cells in Sheet with Merged and Hidden Cells | Excel Discussion (Misc queries) | |||
sum visible cells | Excel Discussion (Misc queries) | |||
Sum visible cells only | Excel Discussion (Misc queries) | |||
Sum only visible cells | Excel Programming | |||
Help: Copying Visible Cells only to Visible cells! | Excel Programming |