If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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" CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in message news:CaptainQuattro.277voy_1146635100.8876@excelfo 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
|
|||
|
|||
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" CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in message news:CaptainQuattro.277voy_1146635100.8876@excelfo 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
|
|||
|
|||
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" CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in message news:CaptainQuattro.277voy_1146635100.8876@excelfo 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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula: If 2 values (in a range of six) are >3 then TRUE, FALSE | IFfunction | General Discussion | 2 | October 10th, 2005 10:34 AM |
Summing values within a range | rmellison | General Discussion | 7 | September 2nd, 2005 12:43 PM |
Cell linked to a range of cell values in different sheet | szeng | General Discussion | 1 | August 9th, 2005 02:41 AM |
Want to put range values in a column into two columns | Arch | Worksheet Functions | 7 | June 15th, 2004 11:49 PM |
Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP | Bob Lidgard | Charts and Charting | 14 | February 25th, 2004 03:26 PM |