only sum on visible columns

  • Thread starter Thread starter dwojtowi
  • Start date Start date
D

dwojtowi

Hello,
I have a macro that will hide the columns that are not necessar
(similiar to the way a filter would, but a little more advanced).
would like to do a sum based only on the visible columns. Is thi
possible (similar to a subtotal() command)
Thanks,
Dav
 
Hi Dave
if you have Excel 2003 you may use the subtotal function. e.g.
=SUBTOTAL(109,A1:A1000)
instead of
=SUBTOTAL(9,A1:A1000)

i'm not sure if this feature also exist prior to Excel 2003
 
xl2002 has the =subtotal(9,a1:a1000), but it will ignore only those rows hidden
by filtering--not manually hiding.

But the OP could use specialcells to get that sum.

dim myRng as range
set myRng = nothing
on error resume next
set myrng = activesheet.range("a1:a1000").cells.specialcells(xlcelltypevisible)
on error goto 0

if myrng is nothing then
msgbox "It's 0."
else
msgbox application.sum(myrng)
end if
 
Back
Top