Summing non hidden values in a range

[starguy]

I have data in a range of D11:K11 and its sum in cell L11. This goes down to around 30 rows. Sometimes I have to hide columns in range of D11:K11 and want to sum the non hidden cells in L11 and down to 30 rows.
Is there any formula or function???

[Biff]

Hi!

What version of Excel are you using?

If you're using Excel 2003:

=SUBTOTAL(109,D11:K11)

If you're using any other version I think you may need a UDF.

Biff

"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> I have data in a range of D11:K11 and its sum in cell L11. This goes
> down to around 30 rows. Sometimes I have to hide columns in range of
> D11:K11 and want to sum the non hidden cells in L11 and down to 30
> rows.
> Is there any formula or function???
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
> http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=537953
>

[CaptainQuattro]

I have data in a range of D11:K11 and its sum in cell L11. This goes down to around 30 rows. Sometimes I have to hide columns in range of D11:K11 and want to sum the non hidden cells in L11 and down to 30 rows.
Is there any formula or function???

Try =SUBTOTAL(9,D11:K11)

[starguy]

using Excel 2003 but it did not work.

[JB]

With UDF (Excel <2003)


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


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

Cordialy JB

[starguy]

thanks for reply but I dont know to implement UDF because I dont know VB.
I m using Excel 2003 then why this function does not work???

[Bob Phillips]

How have you hidden the rows?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> thanks for reply but I dont know to implement UDF because I dont know
> VB.
> I m using Excel 2003 then why this function does not work???
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=537953
>

[starguy]

I have hidden columns not rows. I hide them by both ways using Ctrl+9 and by menu Format > Column > Hide but nothing happened after hiding. I also pressed F9 to recalculate after hiding columns.


[QUOTE=Bob Phillips]How have you hidden the rows?

--
HTH

Bob Phillips

[Bob Phillips]

Something like =SUBTOTAL(109,A1:E1) should work in those circumstances.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> I have hidden columns not rows. I hide them by both ways using Ctrl+9
> and by menu Format > Column > Hide but nothing happened after hiding. I
> also pressed F9 to recalculate after hiding columns.
>
>
> Bob Phillips Wrote:
> > How have you hidden the rows?
> >
> > --
> > HTH
> >
> > Bob Phillips
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=537953
>

[starguy]

its not working. i m sending you sample workbook. please check what is the problem.

[Bob Phillips]

Afraid I can't, I don't have 2003.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> its not working. i m sending you sample workbook. please check what is
> the problem.
>
>
> +-------------------------------------------------------------------+
> |Filename: Book1.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4714 |
> +-------------------------------------------------------------------+
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=537953
>

[starguy]

what should I do. I need it... any body esle...

[Ardus Petus]

I don't have XL 2003 either..

Sounds like you need an UDF.

Here is some code you can paste in a Module

'------
Function TOTAL_VISIBLE(rng As Range) As Long
Dim c As Range
For Each c In rng
With c
If Not .EntireColumn.Hidden Then
TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
End If
End With
Next c
End Function
'---------

"starguy" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> what should I do. I need it... any body esle...
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
> http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=537953
>

[Bob Phillips]

Problem with a UDF is that it is not recalculated if a new row gets
hidden/unhidden. You would have to force it somehow.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> I don't have XL 2003 either..
>
> Sounds like you need an UDF.
>
> Here is some code you can paste in a Module
>
> '------
> Function TOTAL_VISIBLE(rng As Range) As Long
> Dim c As Range
> For Each c In rng
> With c
> If Not .EntireColumn.Hidden Then
> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
> End If
> End With
> Next c
> End Function
> '---------
>
> "starguy" <[email protected]> a écrit
dans
> le message de news: [email protected]...
> >
> > what should I do. I need it... any body esle...
> >
> >
> > --
> > starguy
> > ------------------------------------------------------------------------
> > starguy's Profile:
> > http://www.excelforum.com/member.php...o&userid=32434
> > View this thread:
http://www.excelforum.com/showthread...hreadid=537953
> >
>
>

[Dave Peterson]

=subtotal(109,....) works nicely with hidden rows--not so nicely with hidden
columns in xl2003.

Bob Phillips wrote:
>
> Something like =SUBTOTAL(109,A1:E1) should work in those circumstances.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "starguy" <[email protected]> wrote in
> message news:[email protected]...
> >
> > I have hidden columns not rows. I hide them by both ways using Ctrl+9
> > and by menu Format > Column > Hide but nothing happened after hiding. I
> > also pressed F9 to recalculate after hiding columns.
> >
> >
> > Bob Phillips Wrote:
> > > How have you hidden the rows?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> >
> >
> > --
> > starguy
> > ------------------------------------------------------------------------
> > starguy's Profile:
> http://www.excelforum.com/member.php...o&userid=32434
> > View this thread: http://www.excelforum.com/showthread...hreadid=537953
> >

--

Dave Peterson

[Bob Phillips]

Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine with
columns?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> =subtotal(109,....) works nicely with hidden rows--not so nicely with
hidden
> columns in xl2003.
>
> Bob Phillips wrote:
> >
> > Something like =SUBTOTAL(109,A1:E1) should work in those circumstances.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "starguy" <[email protected]> wrote
in
> > message news:[email protected]...
> > >
> > > I have hidden columns not rows. I hide them by both ways using Ctrl+9
> > > and by menu Format > Column > Hide but nothing happened after hiding.
I
> > > also pressed F9 to recalculate after hiding columns.
> > >
> > >
> > > Bob Phillips Wrote:
> > > > How have you hidden the rows?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > >
> > >
> > > --
> > > starguy
> >
> ------------------------------------------------------------------------
> > > starguy's Profile:
> > http://www.excelforum.com/member.php...o&userid=32434
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=537953
> > >
>
> --
>
> Dave Peterson

[Dave Peterson]

=subtotal(9,a1:e1)
didn't exclude cells in hidden columns for me in xl2003.

(Did you really mean to type columns in your question?)



Bob Phillips wrote:
>
> Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine with
> columns?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > =subtotal(109,....) works nicely with hidden rows--not so nicely with
> hidden
> > columns in xl2003.
> >
> > Bob Phillips wrote:
> > >
> > > Something like =SUBTOTAL(109,A1:E1) should work in those circumstances.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "starguy" <[email protected]> wrote
> in
> > > message news:[email protected]...
> > > >
> > > > I have hidden columns not rows. I hide them by both ways using Ctrl+9
> > > > and by menu Format > Column > Hide but nothing happened after hiding.
> I
> > > > also pressed F9 to recalculate after hiding columns.
> > > >
> > > >
> > > > Bob Phillips Wrote:
> > > > > How have you hidden the rows?
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > >
> > > >
> > > > --
> > > > starguy
> > >
> > ------------------------------------------------------------------------
> > > > starguy's Profile:
> > > http://www.excelforum.com/member.php...o&userid=32434
> > > > View this thread:
> http://www.excelforum.com/showthread...hreadid=537953
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Bob Phillips]

I am not sure what I was thinking now, as it couldn't have worked. You can't
filter a row, so if anything in A1:E1 is filtered, it all is. Oh well. Poor
implementation though, they obviously just carried the existing
functionality, without considering that whilst columns may not get hidden by
filter, they can by manually hiding.

Bob

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> =subtotal(9,a1:e1)
> didn't exclude cells in hidden columns for me in xl2003.
>
> (Did you really mean to type columns in your question?)
>
>
>
> Bob Phillips wrote:
> >
> > Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine
with
> > columns?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > =subtotal(109,....) works nicely with hidden rows--not so nicely with
> > hidden
> > > columns in xl2003.
> > >
> > > Bob Phillips wrote:
> > > >
> > > > Something like =SUBTOTAL(109,A1:E1) should work in those
circumstances.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "starguy" <[email protected]>
wrote
> > in
> > > > message news:[email protected]...
> > > > >
> > > > > I have hidden columns not rows. I hide them by both ways using
Ctrl+9
> > > > > and by menu Format > Column > Hide but nothing happened after
hiding.
> > I
> > > > > also pressed F9 to recalculate after hiding columns.
> > > > >
> > > > >
> > > > > Bob Phillips Wrote:
> > > > > > How have you hidden the rows?
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > >
> > > > >
> > > > > --
> > > > > starguy
> > > >
> >
> ------------------------------------------------------------------------
> > > > > starguy's Profile:
> > > > http://www.excelforum.com/member.php...o&userid=32434
> > > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=537953
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

[Dave Peterson]

It sure seems like a small step (to me, anyway) to make =subtotal(1##,...) work
with hidden columns.

But who the heck knows, well outside of MS?

Bob Phillips wrote:
>
> I am not sure what I was thinking now, as it couldn't have worked. You can't
> filter a row, so if anything in A1:E1 is filtered, it all is. Oh well. Poor
> implementation though, they obviously just carried the existing
> functionality, without considering that whilst columns may not get hidden by
> filter, they can by manually hiding.
>
> Bob
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > =subtotal(9,a1:e1)
> > didn't exclude cells in hidden columns for me in xl2003.
> >
> > (Did you really mean to type columns in your question?)
> >
> >
> >
> > Bob Phillips wrote:
> > >
> > > Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine
> with
> > > columns?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > =subtotal(109,....) works nicely with hidden rows--not so nicely with
> > > hidden
> > > > columns in xl2003.
> > > >
> > > > Bob Phillips wrote:
> > > > >
> > > > > Something like =SUBTOTAL(109,A1:E1) should work in those
> circumstances.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove nothere from email address if mailing direct)
> > > > >
> > > > > "starguy" <[email protected]>
> wrote
> > > in
> > > > > message news:[email protected]...
> > > > > >
> > > > > > I have hidden columns not rows. I hide them by both ways using
> Ctrl+9
> > > > > > and by menu Format > Column > Hide but nothing happened after
> hiding.
> > > I
> > > > > > also pressed F9 to recalculate after hiding columns.
> > > > > >
> > > > > >
> > > > > > Bob Phillips Wrote:
> > > > > > > How have you hidden the rows?
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > >
> > > > > >
> > > > > > --
> > > > > > starguy
> > > > >
> > >
> > ------------------------------------------------------------------------
> > > > > > starguy's Profile:
> > > > > http://www.excelforum.com/member.php...o&userid=32434
> > > > > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=537953
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Biff]

That'll be a new feature in Excel 14.

Biff

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> It sure seems like a small step (to me, anyway) to make =subtotal(1##,...)
> work
> with hidden columns.
>
> But who the heck knows, well outside of MS?
>
> Bob Phillips wrote:
>>
>> I am not sure what I was thinking now, as it couldn't have worked. You
>> can't
>> filter a row, so if anything in A1:E1 is filtered, it all is. Oh well.
>> Poor
>> implementation though, they obviously just carried the existing
>> functionality, without considering that whilst columns may not get hidden
>> by
>> filter, they can by manually hiding.
>>
>> Bob
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > =subtotal(9,a1:e1)
>> > didn't exclude cells in hidden columns for me in xl2003.
>> >
>> > (Did you really mean to type columns in your question?)
>> >
>> >
>> >
>> > Bob Phillips wrote:
>> > >
>> > > Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works
>> > > fine
>> with
>> > > columns?
>> > >
>> > > --
>> > > HTH
>> > >
>> > > Bob Phillips
>> > >
>> > > (remove nothere from email address if mailing direct)
>> > >
>> > > "Dave Peterson" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > =subtotal(109,....) works nicely with hidden rows--not so nicely
>> > > > with
>> > > hidden
>> > > > columns in xl2003.
>> > > >
>> > > > Bob Phillips wrote:
>> > > > >
>> > > > > Something like =SUBTOTAL(109,A1:E1) should work in those
>> circumstances.
>> > > > >
>> > > > > --
>> > > > > HTH
>> > > > >
>> > > > > Bob Phillips
>> > > > >
>> > > > > (remove nothere from email address if mailing direct)
>> > > > >
>> > > > > "starguy" <[email protected]>
>> wrote
>> > > in
>> > > > > message
>> > > > > news:[email protected]...
>> > > > > >
>> > > > > > I have hidden columns not rows. I hide them by both ways using
>> Ctrl+9
>> > > > > > and by menu Format > Column > Hide but nothing happened after
>> hiding.
>> > > I
>> > > > > > also pressed F9 to recalculate after hiding columns.
>> > > > > >
>> > > > > >
>> > > > > > Bob Phillips Wrote:
>> > > > > > > How have you hidden the rows?
>> > > > > > >
>> > > > > > > --
>> > > > > > > HTH
>> > > > > > >
>> > > > > > > Bob Phillips
>> > > > > >
>> > > > > >
>> > > > > > --
>> > > > > > starguy
>> > > > >
>> > >
>> > ------------------------------------------------------------------------
>> > > > > > starguy's Profile:
>> > > > > http://www.excelforum.com/member.php...o&userid=32434
>> > > > > > View this thread:
>> > > http://www.excelforum.com/showthread...hreadid=537953
>> > > > > >
>> > > >
>> > > > --
>> > > >
>> > > > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Dave Peterson]

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

O, ye of little faith.



Biff wrote:
>
> 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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> You're skipping excel 12 and excel 13????
>
> O, ye of little faith.
>
>
>
> Biff wrote:
>>
>> That'll be a new feature in Excel 14.
>>
>> Biff
>>
> <<snipped>>

[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" <[email protected]> wrote in message
news:[email protected]...
>I don't have XL 2003 either..
>
> Sounds like you need an UDF.
>
> Here is some code you can paste in a Module
>
> '------
> Function TOTAL_VISIBLE(rng As Range) As Long
> Dim c As Range
> For Each c In rng
> With c
> If Not .EntireColumn.Hidden Then
> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
> End If
> End With
> Next c
> End Function
> '---------
>
> "starguy" <[email protected]> a écrit
> dans le message de news:
> [email protected]...
>>
>> what should I do. I need it... any body esle...
>>
>>
>> --
>> starguy
>> ------------------------------------------------------------------------
>> starguy's Profile:
>> http://www.excelforum.com/member.php...o&userid=32434
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=537953
>>
>
>

[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

"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> what should I do now...problem persists.
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
> http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=537953
>

[Ardus Petus]

Works fine by me.

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

--
AP

"Biff" <[email protected]> a écrit dans le message de news:
[email protected]...
> Ardus, I tried your UDF but I get a result of 0 all the time (with or
> without hidden columns)
>
> Biff
>
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
>>I don't have XL 2003 either..
>>
>> Sounds like you need an UDF.
>>
>> Here is some code you can paste in a Module
>>
>> '------
>> Function TOTAL_VISIBLE(rng As Range) As Long
>> Dim c As Range
>> For Each c In rng
>> With c
>> If Not .EntireColumn.Hidden Then
>> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
>> End If
>> End With
>> Next c
>> End Function
>> '---------
>>
>> "starguy" <[email protected]> a écrit
>> dans le message de news:
>> [email protected]...
>>>
>>> what should I do. I need it... any body esle...
>>>
>>>
>>> --
>>> starguy
>>> ------------------------------------------------------------------------
>>> starguy's Profile:
>>> http://www.excelforum.com/member.php...o&userid=32434
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=537953
>>>
>>
>>
>
>

[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

"Ardus Petus" <[email protected]> wrote in message
news:%23VQ8J%[email protected]...
> Works fine by me.
>
> Did you enter a valid range? (eg: =total_visible(A1:A4) )
>
> --
> AP
>
> "Biff" <[email protected]> a écrit dans le message de news:
> [email protected]...
>> Ardus, I tried your UDF but I get a result of 0 all the time (with or
>> without hidden columns)
>>
>> Biff
>>
>> "Ardus Petus" <[email protected]> wrote in message
>> news:[email protected]...
>>>I don't have XL 2003 either..
>>>
>>> Sounds like you need an UDF.
>>>
>>> Here is some code you can paste in a Module
>>>
>>> '------
>>> Function TOTAL_VISIBLE(rng As Range) As Long
>>> Dim c As Range
>>> For Each c In rng
>>> With c
>>> If Not .EntireColumn.Hidden Then
>>> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
>>> End If
>>> End With
>>> Next c
>>> End Function
>>> '---------
>>>
>>> "starguy" <[email protected]> a écrit
>>> dans le message de news:
>>> [email protected]...
>>>>
>>>> what should I do. I need it... any body esle...
>>>>
>>>>
>>>> --
>>>> starguy
>>>> ------------------------------------------------------------------------
>>>> starguy's Profile:
>>>> http://www.excelforum.com/member.php...o&userid=32434
>>>> View this thread:
>>>> http://www.excelforum.com/showthread...hreadid=537953
>>>>
>>>
>>>
>>
>>
>
>

[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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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)
>
>
> --
> CaptainQuattro
> ------------------------------------------------------------------------
> CaptainQuattro's Profile:
http://www.excelforum.com/member.php...o&userid=32763
> View this thread: http://www.excelforum.com/showthread...hreadid=537953
>

[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.



Biff wrote:
>
> > 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
>
> "Ardus Petus" <[email protected]> wrote in message
> news:%23VQ8J%[email protected]...
> > Works fine by me.
> >
> > Did you enter a valid range? (eg: =total_visible(A1:A4) )
> >
> > --
> > AP
> >
> > "Biff" <[email protected]> a écrit dans le message de news:
> > [email protected]...
> >> Ardus, I tried your UDF but I get a result of 0 all the time (with or
> >> without hidden columns)
> >>
> >> Biff
> >>
> >> "Ardus Petus" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>I don't have XL 2003 either..
> >>>
> >>> Sounds like you need an UDF.
> >>>
> >>> Here is some code you can paste in a Module
> >>>
> >>> '------
> >>> Function TOTAL_VISIBLE(rng As Range) As Long
> >>> Dim c As Range
> >>> For Each c In rng
> >>> With c
> >>> If Not .EntireColumn.Hidden Then
> >>> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
> >>> End If
> >>> End With
> >>> Next c
> >>> End Function
> >>> '---------
> >>>
> >>> "starguy" <[email protected]> a écrit
> >>> dans le message de news:
> >>> [email protected]...
> >>>>
> >>>> what should I do. I need it... any body esle...
> >>>>
> >>>>
> >>>> --
> >>>> starguy
> >>>> ------------------------------------------------------------------------
> >>>> starguy's Profile:
> >>>> http://www.excelforum.com/member.php...o&userid=32434
> >>>> View this thread:
> >>>> http://www.excelforum.com/showthread...hreadid=537953
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >

--

Dave Peterson

[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.

starguy wrote:
>
> what should I do now...problem persists.
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=537953

--

Dave Peterson

[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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>
> Biff wrote:
> >
> > > 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
> >
> > "Ardus Petus" <[email protected]> wrote in message
> > news:%23VQ8J%[email protected]...
> > > Works fine by me.
> > >
> > > Did you enter a valid range? (eg: =total_visible(A1:A4) )
> > >
> > > --
> > > AP
> > >
> > > "Biff" <[email protected]> a écrit dans le message de news:
> > > [email protected]...
> > >> Ardus, I tried your UDF but I get a result of 0 all the time (with or
> > >> without hidden columns)
> > >>
> > >> Biff
> > >>
> > >> "Ardus Petus" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >>>I don't have XL 2003 either..
> > >>>
> > >>> Sounds like you need an UDF.
> > >>>
> > >>> Here is some code you can paste in a Module
> > >>>
> > >>> '------
> > >>> Function TOTAL_VISIBLE(rng As Range) As Long
> > >>> Dim c As Range
> > >>> For Each c In rng
> > >>> With c
> > >>> If Not .EntireColumn.Hidden Then
> > >>> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
> > >>> End If
> > >>> End With
> > >>> Next c
> > >>> End Function
> > >>> '---------
> > >>>
> > >>> "starguy" <[email protected]> a
écrit
> > >>> dans le message de news:
> > >>> [email protected]...
> > >>>>
> > >>>> what should I do. I need it... any body esle...
> > >>>>
> > >>>>
> > >>>> --
> > >>>> starguy
> >
>>>> -----------------------------------------------------------------------
-
> > >>>> starguy's Profile:
> > >>>> http://www.excelforum.com/member.php...o&userid=32434
> > >>>> View this thread:
> > >>>> http://www.excelforum.com/showthread...hreadid=537953
> > >>>>
> > >>>
> > >>>
> > >>
> > >>
> > >
> > >
>
> --
>
> Dave Peterson

[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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>
> Biff wrote:
> >
> > > 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
> >
> > "Ardus Petus" <[email protected]> wrote in message
> > news:%23VQ8J%[email protected]...
> > > Works fine by me.
> > >
> > > Did you enter a valid range? (eg: =total_visible(A1:A4) )
> > >
> > > --
> > > AP
> > >
> > > "Biff" <[email protected]> a écrit dans le message de news:
> > > [email protected]...
> > >> Ardus, I tried your UDF but I get a result of 0 all the time (with or
> > >> without hidden columns)
> > >>
> > >> Biff
> > >>
> > >> "Ardus Petus" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >>>I don't have XL 2003 either..
> > >>>
> > >>> Sounds like you need an UDF.
> > >>>
> > >>> Here is some code you can paste in a Module
> > >>>
> > >>> '------
> > >>> Function TOTAL_VISIBLE(rng As Range) As Long
> > >>> Dim c As Range
> > >>> For Each c In rng
> > >>> With c
> > >>> If Not .EntireColumn.Hidden Then
> > >>> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
> > >>> End If
> > >>> End With
> > >>> Next c
> > >>> End Function
> > >>> '---------
> > >>>
> > >>> "starguy" <[email protected]> a
écrit
> > >>> dans le message de news:
> > >>> [email protected]...
> > >>>>
> > >>>> what should I do. I need it... any body esle...
> > >>>>
> > >>>>
> > >>>> --
> > >>>> starguy
> >
>>>> -----------------------------------------------------------------------
-
> > >>>> starguy's Profile:
> > >>>> http://www.excelforum.com/member.php...o&userid=32434
> > >>>> View this thread:
> > >>>> http://www.excelforum.com/showthread...hreadid=537953
> > >>>>
> > >>>
> > >>>
> > >>
> > >>
> > >
> > >
>
> --
>
> Dave Peterson

[Dave Peterson]

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

Bob Phillips wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> >
> > Biff wrote:
> > >
> > > > 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
> > >
> > > "Ardus Petus" <[email protected]> wrote in message
> > > news:%23VQ8J%[email protected]...
> > > > Works fine by me.
> > > >
> > > > Did you enter a valid range? (eg: =total_visible(A1:A4) )
> > > >
> > > > --
> > > > AP
> > > >
> > > > "Biff" <[email protected]> a écrit dans le message de news:
> > > > [email protected]...
> > > >> Ardus, I tried your UDF but I get a result of 0 all the time (with or
> > > >> without hidden columns)
> > > >>
> > > >> Biff
> > > >>
> > > >> "Ardus Petus" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >>>I don't have XL 2003 either..
> > > >>>
> > > >>> Sounds like you need an UDF.
> > > >>>
> > > >>> Here is some code you can paste in a Module
> > > >>>
> > > >>> '------
> > > >>> Function TOTAL_VISIBLE(rng As Range) As Long
> > > >>> Dim c As Range
> > > >>> For Each c In rng
> > > >>> With c
> > > >>> If Not .EntireColumn.Hidden Then
> > > >>> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
> > > >>> End If
> > > >>> End With
> > > >>> Next c
> > > >>> End Function
> > > >>> '---------
> > > >>>
> > > >>> "starguy" <[email protected]> a
> écrit
> > > >>> dans le message de news:
> > > >>> [email protected]...
> > > >>>>
> > > >>>> what should I do. I need it... any body esle...
> > > >>>>
> > > >>>>
> > > >>>> --
> > > >>>> starguy
> > >
> >>>> -----------------------------------------------------------------------
> -
> > > >>>> starguy's Profile:
> > > >>>> http://www.excelforum.com/member.php...o&userid=32434
> > > >>>> View this thread:
> > > >>>> http://www.excelforum.com/showthread...hreadid=537953
> > > >>>>
> > > >>>
> > > >>>
> > > >>
> > > >>
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Bob Phillips]

Nope, no good in 2000 or 97.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> xl2003, too. But I don't recall if that ever changed from previous
versions.
>
> Bob Phillips wrote:
> >
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> > >
> > > Biff wrote:
> > > >
> > > > > 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
> > > >
> > > > "Ardus Petus" <[email protected]> wrote in message
> > > > news:%23VQ8J%[email protected]...
> > > > > Works fine by me.
> > > > >
> > > > > Did you enter a valid range? (eg: =total_visible(A1:A4) )
> > > > >
> > > > > --
> > > > > AP
> > > > >
> > > > > "Biff" <[email protected]> a écrit dans le message de news:
> > > > > [email protected]...
> > > > >> Ardus, I tried your UDF but I get a result of 0 all the time
(with or
> > > > >> without hidden columns)
> > > > >>
> > > > >> Biff
> > > > >>
> > > > >> "Ardus Petus" <[email protected]> wrote in message
> > > > >> news:[email protected]...
> > > > >>>I don't have XL 2003 either..
> > > > >>>
> > > > >>> Sounds like you need an UDF.
> > > > >>>
> > > > >>> Here is some code you can paste in a Module
> > > > >>>
> > > > >>> '------
> > > > >>> Function TOTAL_VISIBLE(rng As Range) As Long
> > > > >>> Dim c As Range
> > > > >>> For Each c In rng
> > > > >>> With c
> > > > >>> If Not .EntireColumn.Hidden Then
> > > > >>> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
> > > > >>> End If
> > > > >>> End With
> > > > >>> Next c
> > > > >>> End Function
> > > > >>> '---------
> > > > >>>
> > > > >>> "starguy" <[email protected]>
a
> > écrit
> > > > >>> dans le message de news:
> > > > >>> [email protected]...
> > > > >>>>
> > > > >>>> what should I do. I need it... any body esle...
> > > > >>>>
> > > > >>>>
> > > > >>>> --
> > > > >>>> starguy
> > > >
> >
>>>> -----------------------------------------------------------------------
> > -
> > > > >>>> starguy's Profile:
> > > > >>>>
http://www.excelforum.com/member.php...o&userid=32434
> > > > >>>> View this thread:
> > > > >>>> http://www.excelforum.com/showthread...hreadid=537953
> > > > >>>>
> > > > >>>
> > > > >>>
> > > > >>
> > > > >>
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

[Dave Peterson]

Thanks for testing.

(I'll try to remember it.)

Bob Phillips wrote:
>
> Nope, no good in 2000 or 97.
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > xl2003, too. But I don't recall if that ever changed from previous
> versions.
> >
> > Bob Phillips wrote:
> > >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> > > >
> > > >
> > > > Biff wrote:
> > > > >
> > > > > > 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
> > > > >
> > > > > "Ardus Petus" <[email protected]> wrote in message
> > > > > news:%23VQ8J%[email protected]...
> > > > > > Works fine by me.
> > > > > >
> > > > > > Did you enter a valid range? (eg: =total_visible(A1:A4) )
> > > > > >
> > > > > > --
> > > > > > AP
> > > > > >
> > > > > > "Biff" <[email protected]> a écrit dans le message de news:
> > > > > > [email protected]...
> > > > > >> Ardus, I tried your UDF but I get a result of 0 all the time
> (with or
> > > > > >> without hidden columns)
> > > > > >>
> > > > > >> Biff
> > > > > >>
> > > > > >> "Ardus Petus" <[email protected]> wrote in message
> > > > > >> news:[email protected]...
> > > > > >>>I don't have XL 2003 either..
> > > > > >>>
> > > > > >>> Sounds like you need an UDF.
> > > > > >>>
> > > > > >>> Here is some code you can paste in a Module
> > > > > >>>
> > > > > >>> '------
> > > > > >>> Function TOTAL_VISIBLE(rng As Range) As Long
> > > > > >>> Dim c As Range
> > > > > >>> For Each c In rng
> > > > > >>> With c
> > > > > >>> If Not .EntireColumn.Hidden Then
> > > > > >>> TOTAL_VISIBLE = TOTAL_VISIBLE + .Value
> > > > > >>> End If
> > > > > >>> End With
> > > > > >>> Next c
> > > > > >>> End Function
> > > > > >>> '---------
> > > > > >>>
> > > > > >>> "starguy" <[email protected]>
> a
> > > écrit
> > > > > >>> dans le message de news:
> > > > > >>> [email protected]...
> > > > > >>>>
> > > > > >>>> what should I do. I need it... any body esle...
> > > > > >>>>
> > > > > >>>>
> > > > > >>>> --
> > > > > >>>> starguy
> > > > >
> > >
> >>>> -----------------------------------------------------------------------
> > > -
> > > > > >>>> starguy's Profile:
> > > > > >>>>
> http://www.excelforum.com/member.php...o&userid=32434
> > > > > >>>> View this thread:
> > > > > >>>> http://www.excelforum.com/showthread...hreadid=537953
> > > > > >>>>
> > > > > >>>
> > > > > >>>
> > > > > >>
> > > > > >>
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Can you explain that, it doesn't work for me?
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "CaptainQuattro"
> <[email protected]> wrote in
> message
> news:[email protected]...
>>
>> 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)
>>
>>
>> --
>> CaptainQuattro
>> ------------------------------------------------------------------------
>> CaptainQuattro's Profile:
> http://www.excelforum.com/member.php...o&userid=32763
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=537953
>>
>
>

[Dominic]

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. ;-)



"Bob Phillips" wrote:

> Can you explain that, it doesn't work for me?
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "CaptainQuattro"
> <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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)
> >
> >
> > --
> > CaptainQuattro
> > ------------------------------------------------------------------------
> > CaptainQuattro's Profile:
> http://www.excelforum.com/member.php...o&userid=32763
> > View this thread: http://www.excelforum.com/showthread...hreadid=537953
> >
>
>
>

[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)

"Dominic" <[email protected]> wrote in message
news:[email protected]...
> 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. ;-)
>
>
>
> "Bob Phillips" wrote:
>
> > Can you explain that, it doesn't work for me?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "CaptainQuattro"
> > <[email protected]> wrote in
> > message
news:[email protected]...
> > >
> > > 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)
> > >
> > >
> > > --
> > > CaptainQuattro
> >
> ------------------------------------------------------------------------
> > > CaptainQuattro's Profile:
> > http://www.excelforum.com/member.php...o&userid=32763
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=537953
> > >
> >
> >
> >

[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