Selecting totals from UNHIDDEN columns Only


Board Regular
Oct 17, 2007

I have a workbook that has 2 sheets; "Quote Summary" and "ECS". The "ECS" sheet contains columns "C" through "L" each of which has a total on row "27". At any time, one or several (but never All) of these columns may be unused and therefore hidden.

I would like to know if it is possible to sum the total of only those columns that are NOT hidden and display that value in cel "C15" of the "Quote_Summary" sheet.

Thanks in advance for your help

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Perhaps next UDF could help.
Option Explicit
Function SUM_NOT_HIDDEN() As Double
Dim I As Integer
    For I = 3 To 12
        If (Sheets("ECS").Cells(27, I).EntireColumn.Hidden = False) Then SUM_NOT_HIDDEN = SUM_NOT_HIDDEN + Sheets("ECS").Cells(27, I)
    Next I
End Function
and to refresh the formula without to do it manually.
Install next code in Quote Summary sheet code
Option Explicit
Private Sub Worksheet_Activate()
Dim MyFORMULA As String
    MyFORMULA = Range("C15").FormulaR1C1
    Range("C15").FormulaR1C1 = MyFORMULA
End Sub
Upvote 0
Thank you PCL,

As a relative newbe, where is it I should put the UDF code? Would I put that on the code sheet of the ECS page?
Upvote 0
The UDF code must be placed in a module.
An easy way to do (not very smart) can be to record a macro: Tool>macro>Record New Macro
and close it immediately.
Then Tool>Macro>Edit
and replace the macro code by the UDF one
Upvote 0

I have placed the UDF code in a module by replacing a macro code as you suggested and I have added the refresh code on the code sheet of the Summary page.

When I look at the field c15 on the summary page, i get no value. What is it that triggers the UDF code?

I think what i am missing is what triggers these 2 pieces of code. Is it supposed to just always be working so that Summary C15 will display a total of all columns and change as coumns are hidden?
Upvote 0
I forgot to mention
In sheet Quote Summary
Upvote 0
Ok...Now we are getting somewhere. :)

I set the Summary C15 cell =SUM_NOT_HIDDEN()
but now I get an error message that reads "A value used in the formula is of the wrong data type"

Any ideas?
Upvote 0
The UDF is summing data from sheet ECS column 3 to 12 in row 27.
It means it must exist only numbers in these cells.
In sheet Quote Summary cell C15 must be format as general or number.
Is it what you have???
Note: We could have a range mentioned in the UDF argument if you want to sum somewhere else than C27:L27
Upvote 0

I found it, I had a typo. Please excuse my last reply.

The value 332 now shows in the Summary C15 cell. This is the correct sum for all of the columns when none are hidden.

I do have another 2 issues.

1) When I hide a column on the ECS sheet, the value on Summary 315 does not change accordingly.
2) When I try to use the macro which appears just above the function you gave me, I get an error stating that "only comments can appear beneath End Sub because the "Option Explicit" appares above the separation line. How do I get that beneath the line?
Upvote 0
PCL, You are so patient and I thank you for that.

I think I answered one of my questions. I added a module and pasted your code there instead of where it was and now the Hide macro I was using no longer gets the error.

I still do however have the issue of the value not changing on Summary C15 when I hide a column.

Also, would there be a way to alter the C15 part so that if the value is = to 332, it displays a "0" and only displays the sum of all unhidden columns from the summary page if the total is not 332?

Maybe I am asking too much of Excel?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back