Sum Visible Cells

[Ricky Pang]

Hello,
I have numerous columns with subtotals. When a few columns are hidden,
what's the formula that activately adjusts itself to add only the visible
cells?

Much appreciated,
Ricky

 

[Ricky Pang]

Hi Ken,
Unfortunately, I do use the manual hide columns function very often. So is
there another alternative to this Subtotal function that will accomodate my
purpose? perhaps in VB code?

Thanks again,
Ricky

 

[Ken Wright]

Apologies, I've just seen that you actually said columns and not rows. Other than make the tea,
you can do just about anything in VBA, but it's usually better to examine what you are doing in
the first place and see if you can prevent the problem, rather than try and fix the effects.

How is your data laid out, what kind of data is it, and what kind of columns would you hide and
why?

 

[Don Guillett]

try this
Sub sumvis()
mysum = 0
On Error Resume Next
For Each c In [a1:a10]
If c.EntireRow.Hidden <> True Then
mysum = mysum + c.Value
End If
Next
MsgBox mysum
End Sub

 

[Ricky Pang]

Hi Don,
This code doesn't give me the correct sum based on visible cells. I've
sumed up the regular values, hid a few columns, then sumed up the visible
values; yet, I still get the same total each time I run this macro. All of
my values are indeed within cell A1:A10 according to your example, but it
still didn't work. I'm trying to total only the visible cells and have it
shown in a cell.

Thanks,
Ricky

try this
Sub sumvis()
mysum = 0
On Error Resume Next
For Each c In [a1:a10]
If c.EntireRow.Hidden <> True Then
mysum = mysum + c.Value
End If
Next
MsgBox mysum
End Sub

--
Don Guillett
SalesAid Software
Granite Shoals, TX
(e-mail address removed)

Hello,
I have numerous columns with subtotals. When a few columns are hidden,
what's the formula that activately adjusts itself to add only the visible
cells?

Much appreciated,
Ricky

 

[Ricky Pang]

You're right Ken. Doing it as a function would be the ideal choice instead
of VB code.

My spreadsheet consists of multiple columns with a total for each row. Then
I sum each column at the bottom. My criteria to hide a column depends on
what the sum at the bottom is (and my criteria changes often). Now in a
separate cell all by itself.....I want to see the total of the visible cells
sums after I've hidden a few of the columns. How would you go about that?
without using autofilter.

Thanks,
Ricky

 

[Ricky Pang]

You're right Ken. Doing it as a function would be the ideal choice instead
of VB code.

My spreadsheet consists of multiple columns with a total for each row. Then
I sum each column at the bottom. My criteria to hide a column depends on
what the sum at the bottom is (and my criteria changes often). Now in a
separate cell all by itself.....I want to see the total of the visible cells
sums after I've hidden a few of the columns. How would you go about that?
without using autofilter.

Thanks,
Ricky

 

[Don Guillett]

Funny, I just re-tested with auto-filtered cells and with hidden rows and it
worked just fine. xp home, xl2002.

--
Don Guillett
SalesAid Software
Granite Shoals, TX
(e-mail address removed)

Hi Don,
This code doesn't give me the correct sum based on visible cells. I've
sumed up the regular values, hid a few columns, then sumed up the visible
values; yet, I still get the same total each time I run this macro. All of
my values are indeed within cell A1:A10 according to your example, but it
still didn't work. I'm trying to total only the visible cells and have it
shown in a cell.

Thanks,
Ricky

try this
Sub sumvis()
mysum = 0
On Error Resume Next
For Each c In [a1:a10]
If c.EntireRow.Hidden <> True Then
mysum = mysum + c.Value
End If
Next
MsgBox mysum
End Sub

--
Don Guillett
SalesAid Software
Granite Shoals, TX
(e-mail address removed)

Hello,
I have numerous columns with subtotals. When a few columns are hidden,
what's the formula that activately adjusts itself to add only the visible
cells?

Much appreciated,
Ricky