|
Summing non hidden values in a range
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 |
Summing non hidden values in a range
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" wrote in message ... 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 |
Summing non hidden values in a range
starguy Wrote: 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) -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=537953 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
using Excel 2003 but it did 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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
How have you hidden the rows?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... 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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
Something like =SUBTOTAL(109,A1:E1) should work in those circumstances.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... 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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
Afraid I can't, I don't have 2003.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... 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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
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" 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 |
Summing non hidden values in a range
=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" wrote in message ... 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 |
Summing non hidden values in a range
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" wrote in message ... =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" wrote in message ... 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 |
Summing non hidden values in a range
=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" wrote in message ... =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" wrote in message ... 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 |
Summing non hidden values in a range
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" wrote in message ... =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" wrote in message ... =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" wrote in message ... 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 |
Summing non hidden values in a range
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" wrote in message ... =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" wrote in message ... =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" wrote in message ... 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 |
Summing non hidden values in a range
That'll be a new feature in Excel 14.
Biff "Dave Peterson" wrote in message ... 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" wrote in message ... =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" wrote in message ... =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" wrote in message ... 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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
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" wrote in message ... 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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
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" wrote in message ... 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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
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 |
Summing non hidden values in a range
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 |
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. 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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 09:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com