Summing non hidden values in a range

[Dave Peterson]

You're skipping excel 12 and excel 13????

O, ye of little faith.

That'll be a new feature in Excel 14.

Biff

<<snipped>>

 

[Biff]

You're skipping excel 12 and excel 13????

12's already "in the box". I think they may be superstitious and skip 13 and
go right to 14.

Biff

 

[starguy]

what should I do now...problem persists.

 

[Biff]

Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

 

[Ardus Petus]

Works fine by me.

Did you enter a valid range? (eg: =total_visible(A1:A4) )

 

[Biff]

Try JB's UDF.

But note that hidding/unhidding columns/rows does not trigger a calculation
so the formula will not update until a calculation is either automatically
triggered or you manually calculate by hitting function key F9.

Biff

 

[Biff]

Did you enter a valid range? (eg: =total_visible(A1:A4) )

Yes. I got it to return a sum but it doesn't change when columns are hidden
and I calculate.

I had changed the function name but didn't realize it was called later in
the procedure. I changed it back.

Biff

 

[CaptainQuattro]

Here's a workaround that will work in any version of Excel:


In row 1 Column D enter =CELL("width",A2)

Copy to cells E1 through K1

in Cell L11 enter

=SUMIF(D1:K1,">0",D11:K11

 

[Bob Phillips]

Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"

 

[Dave Peterson]

Try adding:

Application.volatile

to the top of the procedure:

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

.....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.

 

[Dave Peterson]

Try adding:

Application.volatile

to the top of the procedure:

Function TOTAL_VISIBLE(rng As Range) As Long
application.volatile

.....


This tells excel to recalculate the function whenever excel recalculates.

If I recall correctly, some versions of excel won't recalc when columns are
hidden/unhidden--maybe all versions??? (Changing the columnwidth--not
hiding/showing--causes a recalc in xl2003, though.)

So for UDFs like these, you'll want to force a recalc before you trust the
results.

 

[Bob Phillips]

Still need to force a recalc, at least in XP you do.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Dave Peterson]

xl2003, too. But I don't recall if that ever changed from previous versions.

 

[Bob Phillips]

Nope, no good in 2000 or 97.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Dave Peterson]

Thanks for testing.

(I'll try to remember it.)

Nope, no good in 2000 or 97.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Biff]

CaptainQuattro may be on to something!

This could be done with GET.CELL but if a working UDF is available I'd go
with it.

Biff

 

[Guest]

Bob,

I got this to work for me by changing the formula in D1 to:

=CELL("width",D2)

For Excel2k (at least) you need to force a recalc to get the totals in
column L to refresh.

I imagine that when you hide column X the value in X1 turns to zero, though
its hard to see to confirm. ;-)

Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"

 

[Bob Phillips]

Exactly, you need to force a recalc, which is the same as the UDF. So it is
no better, and requires a lot of other formulae scattered about. Your
assumption on the value is exactly what I assumed, and can easily be checked
by doing
?range("D1").Value
in the immediate window in the VBIDE, after forcing the recalc of course,
and indeed it does show 0.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[JB]

http://cjoint.com/?fewLLijimL

With this instruction, worksheet is calculate:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
calculate
End Sub

Function sumVisibles(champ As Range)
Application.Volatile
t = 0
For Each c In champ
If c.EntireColumn.Hidden = False Then t = t + c.Value
Next c
sumVisibles = t
End Function

Cordialy JB