summing columns when not hidden

[Mark Jennings]

I have a spreadsheet that sums values in a row across several columns.
Also within my spreadsheet is a macro that will hide certain column
when a check box control is selected. I want my summation of column
to sum values only where the columns are visible - so that value
stored in the hidden columns are not calculated.

How would I best accomplish this? Can VB code be added to the macro
and if so, please provide an example

 

[Tom Ogilvy]

You don't give much information on what your summation of columns actually
is. Are you using the sum worksheet function or are you using a macro to
hard code the sum after calculating it. If the latter, you can check if the
column is hidden.
Dim cell as Range, rw as Long
rw = 10
for each cell in Range(cells(rw,col), cells(rw,col+10))
if cell.EntireColumn.Hidden = false then
if isnumeric(cell) then
tot = tot + cell.Value
end if
end if
Next

If you are using a formula in a cell, you can use a similar approach to
write a UDF which you can use instead.

 

[Frank Kabel]

Hi
in addition to Tom's answer: If you have Excel 2003 you may have a look
at the SUBTOTAL function. e.g.
=SUBTOTAL(109,A1:X1)
to sum all non hidden values in the range A1:X1

 

[Frank Kabel]

Hi
sorry. This will work only if you hide rows but not if you hide
columns. So
=SUBTOTAL(109,A1:A100)
would work but
=SUBTOTAL(109,A1:X1)
won't

So use Tom's suggestion

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
in addition to Tom's answer: If you have Excel 2003 you may have a look
at the SUBTOTAL function. e.g.
=SUBTOTAL(109,A1:X1)
to sum all non hidden values in the range A1:X1

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag news:[email protected]...

 

[Mark Jennings]

I didn't see a post from Tom. Could someone repost?
Thanks - Mar

 

[Frank Kabel]

Hi Mark
a repost:

-----
You don't give much information on what your summation of columns
actually
is. Are you using the sum worksheet function or are you using a macro
to
hard code the sum after calculating it. If the latter, you can check
if the
column is hidden.
Dim cell as Range, rw as Long
rw = 10
for each cell in Range(cells(rw,col), cells(rw,col+10))
if cell.EntireColumn.Hidden = false then
if isnumeric(cell) then
tot = tot + cell.Value
end if
end if
Next

If you are using a formula in a cell, you can use a similar approach to
write a UDF which you can use instead.

 

[Mark Jennings]

Sorry for the ambiguity.

I have a formula in the column where the summation lives.

For each row, the summation is E+F+I+J when all columns are visible.
When columns F and J are hidden the summation is, of course, E+I.

I found that I could designate a cell to contain the value of the chec
box (TRUE/FALSE). Then I converted the formula to If True E+F+I+J
else E + I

However, I am still open to better suggestions/VB examples if anyon
wants to offer

 

[Tom Ogilvy]

Your solution is as good as any and probably better than most if you are
willing to accept the value of the checkbox in the linked cell.