Hidden Columns

[bigfoot]

hi
i have columns G through P, with values on the rows and a total in the column. i have a total in column Q, which sums the totals in all the columns. at times the last 3 columns are hidden. how do i adjust the total in column Q to only sum the visible columns. thanks for the help.

 

[baitmaster]

try replacing =SUM(xxx) with = SUBTOTAL(9,xxx)

 

[bigfoot]

thanks for the response. i tried it, as well as using 109, and it didnt work.

 

[baitmaster]

it definitely works for autofiltered data sets, apparently not if you just hide sections though. You could use a UDF for this, but its not ideal

Can you reset the calculation whenever you hide/show the column instead? How does this feature work?

 

[jim may]

The Subtotal() builtin function seems to only work on ROWS -- Not Columns
A UDF is necessary (it seems) - Paste the below into a standard module and in your
worksheet column Q - first data row (say row 2) enter:

=SumVisColumns(G2:P2)

Function SumVisColumns(Rg As Range)
Dim x As Range
Dim tot As Double
  Application.Volatile
  tot = 0
  For Each x In Rg
     If Not x.Columns.Hidden Then
     tot = tot + x
     End If
  Next x
  SumVisColumns = tot
End Function

 

[bigfoot]

thank you very much. the udf worked well.