Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #31   Report Post  
Old May 3rd 06, 01:19 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

Try adding:

Application.volatile

to the top of the procedu

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

  #32   Report Post  
Old May 3rd 06, 02:21 PM posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

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" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

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" wrote in message
...
Works fine by me.

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

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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" a

écrit
dans le message de news:
...

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



  #33   Report Post  
Old May 3rd 06, 02:40 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

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" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

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" wrote in message
...
Works fine by me.

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

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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" a

écrit
dans le message de news:
...

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
  #34   Report Post  
Old May 3rd 06, 02:47 PM posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

Nope, no good in 2000 or 97.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Dave Peterson" wrote in message
...
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" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

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" wrote in message
...
Works fine by me.

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

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time

(with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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"

a
écrit
dans le message de news:
...

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



  #35   Report Post  
Old May 3rd 06, 05:59 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Summing non hidden values in a range

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" wrote in message
...
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" wrote in message
...
Try adding:

Application.volatile

to the top of the procedu

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" wrote in message
...
Works fine by me.

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

--
AP

"Biff" a écrit dans le message de news:
...
Ardus, I tried your UDF but I get a result of 0 all the time

(with or
without hidden columns)

Biff

"Ardus Petus" wrote in message
...
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"

a
écrit
dans le message de news:
...

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


  #36   Report Post  
Old May 3rd 06, 07:05 PM posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Summing non hidden values in a range

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" wrote in message
...
Can you explain that, it doesn't work for me?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"CaptainQuattro"
<[email protected] m-nospam.com wrote in
message
news:[email protected] rum-nospam.com...

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





  #37   Report Post  
Old May 3rd 06, 11:37 PM posted to microsoft.public.excel.misc
Dominic
 
Posts: n/a
Default Summing non hidden values in a range

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] m-nospam.com wrote in
message news:[email protected] rum-nospam.com...

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




  #38   Report Post  
Old May 4th 06, 08:44 AM posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Summing non hidden values in a range

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" wrote in message
...
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] m-nospam.com wrote in
message

news:[email protected] rum-nospam.com...

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






  #39   Report Post  
Old May 4th 06, 09:38 PM posted to microsoft.public.excel.misc
JB
 
Posts: n/a
Default Summing non hidden values in a range

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
summing values appearing in col B when col A has been filtered choc_penguin Excel Worksheet Functions 3 February 3rd 06 11:18 AM
Summing a range that changes Jeff Excel Discussion (Misc queries) 6 January 9th 06 10:57 PM
Sumproduct not working when summing values between two numbers FlamencoKid Excel Worksheet Functions 8 September 13th 05 06:01 PM
Summing a variable range of columns Richard Buttrey Excel Worksheet Functions 9 July 28th 05 06:52 PM
Match function...random search? Les Excel Worksheet Functions 10 July 28th 05 11:54 AM


All times are GMT +1. The time now is 02:26 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017