| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
tried using SUBTOTAL function (XL XP) and it doesn't seem to work
tried using SUBTOTAL function (XL XP) and it doesn't seem to work
=SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? |
|
#2
|
|||
|
|||
|
Jethro,
SUBTOTAL works on filtered data not hidden. You would need a UDF. Here is a previous one from Bernoe Dietrick You can use a UDF, definition below, used like =mySum(A1:A10) Copy the code and paste it into a module in your workbook. Function mySum(inRange As Range) Dim myCell As Range For Each myCell In inRange If Not myCell.EntireRow.Hidden Then mySum = mySum + myCell.Value End If Next myCell End Function -- HTH RP (remove nothere from the email address if mailing direct) "JethroUK©" wrote in message ... tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? |
|
#3
|
|||
|
|||
|
thanx
i tried to write a function to wrap around any other function but i couldn't - the nearest i got was VISIBLE function that replicates SUBTOTAL but works only on visible cells whether filtered or hidden: Function VISIBLE(Function_num As Long, Rnge As Range) Dim cell As Range, vaddress$ Application.Volatile For Each cell In Rnge If Not cell.EntireRow.Hidden _ And Not cell.EntireColumn.Hidden _ Then vaddress$ = vaddress$ & cell.Address & "," Next vaddress$ = Left(vaddress, Len(vaddress) - 1) Select Case Function_num Case 1 VISIBLE = WorksheetFunction.Average(Range(vaddress$)) Case 2 VISIBLE = WorksheetFunction.Count(Range(vaddress$)) Case 3 VISIBLE = WorksheetFunction.CountA(Range(vaddress$)) Case 4 VISIBLE = WorksheetFunction.Max(Range(vaddress$)) Case 5 VISIBLE = WorksheetFunction.Min(Range(vaddress$)) Case 6 VISIBLE = WorksheetFunction.Product(Range(vaddress$)) Case 7 VISIBLE = WorksheetFunction.StDev(Range(vaddress$)) Case 8 VISIBLE = WorksheetFunction.StDevP(Range(vaddress$)) Case 9 VISIBLE = WorksheetFunction.Sum(Range(vaddress$)) Case 10 VISIBLE = WorksheetFunction.Var(Range(vaddress$)) Case 11 VISIBLE = WorksheetFunction.VarP(Range(vaddress$)) Case Else VISIBLE = "Choose a 'Function_num' between 1 and 11" End Select End Function "Bob Phillips" wrote in message ... Jethro, SUBTOTAL works on filtered data not hidden. You would need a UDF. Here is a previous one from Bernoe Dietrick You can use a UDF, definition below, used like =mySum(A1:A10) Copy the code and paste it into a module in your workbook. Function mySum(inRange As Range) Dim myCell As Range For Each myCell In inRange If Not myCell.EntireRow.Hidden Then mySum = mySum + myCell.Value End If Next myCell End Function -- HTH RP (remove nothere from the email address if mailing direct) "JethroUK©" wrote in message ... tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? |
|
#4
|
|||
|
|||
|
How about this
Function VISIBLE(Function_num As Long, Rnge As Range) Dim cell As Range Dim vAddress As Range Application.Volatile If Rnge.Rows.Count = 1 And Rnge.Columns.Count = 1 Then VISIBLE = "Only 1 cell selected" Exit Function ElseIf Rnge.Rows.Count 1 And Rnge.Columns.Count 1 Then VISIBLE = "Select a single row or column" Exit Function End If If Rnge.Rows.Count 1 Then For Each cell In Rnge If Not cell.EntireRow.Hidden Then If vAddress Is Nothing Then Set vAddress = cell Else Set vAddress = Union(vAddress, cell) End If End If Next Else For Each cell In Rnge If Not cell.EntireColumn.Hidden Then If vAddress Is Nothing Then Set vAddress = cell Else Set vAddress = Union(vAddress, cell) End If End If Next End If If Not vAddress Is Nothing Then Select Case Function_num Case 1: VISIBLE = WorksheetFunction.Average(vAddress) Case 2: VISIBLE = WorksheetFunction.Count(vAddress) Case 3: VISIBLE = WorksheetFunction.CountA(vAddress) Case 4: VISIBLE = WorksheetFunction.Max(vAddress) Case 5: VISIBLE = WorksheetFunction.Min(vAddress) Case 6: VISIBLE = WorksheetFunction.Product(vAddress) Case 7: VISIBLE = WorksheetFunction.StDev(vAddress) Case 8: VISIBLE = WorksheetFunction.StDevP(vAddress) Case 9: VISIBLE = WorksheetFunction.Sum(vAddress) Case 10: VISIBLE = WorksheetFunction.Var(vAddress) Case 11: VISIBLE = WorksheetFunction.VarP(vAddress) Case Else: VISIBLE = "Choose a 'Function_num' between 1 and 11" End Select End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "JethroUK©" wrote in message ... thanx i tried to write a function to wrap around any other function but i couldn't - the nearest i got was VISIBLE function that replicates SUBTOTAL but works only on visible cells whether filtered or hidden: Function VISIBLE(Function_num As Long, Rnge As Range) Dim cell As Range, vaddress$ Application.Volatile For Each cell In Rnge If Not cell.EntireRow.Hidden _ And Not cell.EntireColumn.Hidden _ Then vaddress$ = vaddress$ & cell.Address & "," Next vaddress$ = Left(vaddress, Len(vaddress) - 1) Select Case Function_num Case 1 VISIBLE = WorksheetFunction.Average(Range(vaddress$)) Case 2 VISIBLE = WorksheetFunction.Count(Range(vaddress$)) Case 3 VISIBLE = WorksheetFunction.CountA(Range(vaddress$)) Case 4 VISIBLE = WorksheetFunction.Max(Range(vaddress$)) Case 5 VISIBLE = WorksheetFunction.Min(Range(vaddress$)) Case 6 VISIBLE = WorksheetFunction.Product(Range(vaddress$)) Case 7 VISIBLE = WorksheetFunction.StDev(Range(vaddress$)) Case 8 VISIBLE = WorksheetFunction.StDevP(Range(vaddress$)) Case 9 VISIBLE = WorksheetFunction.Sum(Range(vaddress$)) Case 10 VISIBLE = WorksheetFunction.Var(Range(vaddress$)) Case 11 VISIBLE = WorksheetFunction.VarP(Range(vaddress$)) Case Else VISIBLE = "Choose a 'Function_num' between 1 and 11" End Select End Function "Bob Phillips" wrote in message ... Jethro, SUBTOTAL works on filtered data not hidden. You would need a UDF. Here is a previous one from Bernoe Dietrick You can use a UDF, definition below, used like =mySum(A1:A10) Copy the code and paste it into a module in your workbook. Function mySum(inRange As Range) Dim myCell As Range For Each myCell In inRange If Not myCell.EntireRow.Hidden Then mySum = mySum + myCell.Value End If Next myCell End Function -- HTH RP (remove nothere from the email address if mailing direct) "JethroUK©" wrote in message ... tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? |
|
#5
|
|||
|
|||
|
If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced
=subtotal() function: =subtotal(109,a1:a10) "JethroUK©" wrote: tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? -- Dave Peterson |
|
#6
|
|||
|
|||
|
That feature was introduced in Excel 2003 (but maybe it was in your
special version of Excel 2002 <g). Dave Peterson wrote: If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced =subtotal() function: =subtotal(109,a1:a10) "JethroUK©" wrote: tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#7
|
|||
|
|||
|
Oops. You're correct. I forgot.
Sorry, ma'am. Debra Dalgleish wrote: That feature was introduced in Excel 2003 (but maybe it was in your special version of Excel 2002 <g). Dave Peterson wrote: If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced =subtotal() function: =subtotal(109,a1:a10) "JethroUK©" wrote: tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
|
#8
|
|||
|
|||
|
it doesn't work on my works machine (2002 +sp3)
is there any update i can get that includes this feature? "Debra Dalgleish" wrote in message ... That feature was introduced in Excel 2003 (but maybe it was in your special version of Excel 2002 <g). Dave Peterson wrote: If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced =subtotal() function: =subtotal(109,a1:a10) "JethroUK©" wrote: tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#9
|
|||
|
|||
|
neat - i see you have used the Union method - i have been trying to remember
the method that XL uses of comparing 2 ranges (all visible cells & user range) and returns the overlapping range (only the visible cells within the user range) but thinking was as far as i managed to get :o) "Bob Phillips" wrote in message ... How about this Function VISIBLE(Function_num As Long, Rnge As Range) Dim cell As Range Dim vAddress As Range Application.Volatile If Rnge.Rows.Count = 1 And Rnge.Columns.Count = 1 Then VISIBLE = "Only 1 cell selected" Exit Function ElseIf Rnge.Rows.Count 1 And Rnge.Columns.Count 1 Then VISIBLE = "Select a single row or column" Exit Function End If If Rnge.Rows.Count 1 Then For Each cell In Rnge If Not cell.EntireRow.Hidden Then If vAddress Is Nothing Then Set vAddress = cell Else Set vAddress = Union(vAddress, cell) End If End If Next Else For Each cell In Rnge If Not cell.EntireColumn.Hidden Then If vAddress Is Nothing Then Set vAddress = cell Else Set vAddress = Union(vAddress, cell) End If End If Next End If If Not vAddress Is Nothing Then Select Case Function_num Case 1: VISIBLE = WorksheetFunction.Average(vAddress) Case 2: VISIBLE = WorksheetFunction.Count(vAddress) Case 3: VISIBLE = WorksheetFunction.CountA(vAddress) Case 4: VISIBLE = WorksheetFunction.Max(vAddress) Case 5: VISIBLE = WorksheetFunction.Min(vAddress) Case 6: VISIBLE = WorksheetFunction.Product(vAddress) Case 7: VISIBLE = WorksheetFunction.StDev(vAddress) Case 8: VISIBLE = WorksheetFunction.StDevP(vAddress) Case 9: VISIBLE = WorksheetFunction.Sum(vAddress) Case 10: VISIBLE = WorksheetFunction.Var(vAddress) Case 11: VISIBLE = WorksheetFunction.VarP(vAddress) Case Else: VISIBLE = "Choose a 'Function_num' between 1 and 11" End Select End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "JethroUK©" wrote in message ... thanx i tried to write a function to wrap around any other function but i couldn't - the nearest i got was VISIBLE function that replicates SUBTOTAL but works only on visible cells whether filtered or hidden: Function VISIBLE(Function_num As Long, Rnge As Range) Dim cell As Range, vaddress$ Application.Volatile For Each cell In Rnge If Not cell.EntireRow.Hidden _ And Not cell.EntireColumn.Hidden _ Then vaddress$ = vaddress$ & cell.Address & "," Next vaddress$ = Left(vaddress, Len(vaddress) - 1) Select Case Function_num Case 1 VISIBLE = WorksheetFunction.Average(Range(vaddress$)) Case 2 VISIBLE = WorksheetFunction.Count(Range(vaddress$)) Case 3 VISIBLE = WorksheetFunction.CountA(Range(vaddress$)) Case 4 VISIBLE = WorksheetFunction.Max(Range(vaddress$)) Case 5 VISIBLE = WorksheetFunction.Min(Range(vaddress$)) Case 6 VISIBLE = WorksheetFunction.Product(Range(vaddress$)) Case 7 VISIBLE = WorksheetFunction.StDev(Range(vaddress$)) Case 8 VISIBLE = WorksheetFunction.StDevP(Range(vaddress$)) Case 9 VISIBLE = WorksheetFunction.Sum(Range(vaddress$)) Case 10 VISIBLE = WorksheetFunction.Var(Range(vaddress$)) Case 11 VISIBLE = WorksheetFunction.VarP(Range(vaddress$)) Case Else VISIBLE = "Choose a 'Function_num' between 1 and 11" End Select End Function "Bob Phillips" wrote in message ... Jethro, SUBTOTAL works on filtered data not hidden. You would need a UDF. Here is a previous one from Bernoe Dietrick You can use a UDF, definition below, used like =mySum(A1:A10) Copy the code and paste it into a module in your workbook. Function mySum(inRange As Range) Dim myCell As Range For Each myCell In inRange If Not myCell.EntireRow.Hidden Then mySum = mySum + myCell.Value End If Next myCell End Function -- HTH RP (remove nothere from the email address if mailing direct) "JethroUK©" wrote in message ... tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? |
|
#10
|
|||
|
|||
|
No, unfortunately the 100 series arguments for Subtotal aren't available
for Excel 2002. JethroUK wrote: it doesn't work on my works machine (2002 +sp3) is there any update i can get that includes this feature? "Debra Dalgleish" wrote in message ... That feature was introduced in Excel 2003 (but maybe it was in your special version of Excel 2002 <g). Dave Peterson wrote: If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced =subtotal() function: =subtotal(109,a1:a10) "JethroUK©" wrote: tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#11
|
|||
|
|||
|
You could get that expensive patch named Office 2003 <bg.
I screwed up with my earlier response. JethroUK wrote: it doesn't work on my works machine (2002 +sp3) is there any update i can get that includes this feature? "Debra Dalgleish" wrote in message ... That feature was introduced in Excel 2003 (but maybe it was in your special version of Excel 2002 <g). Dave Peterson wrote: If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced =subtotal() function: =subtotal(109,a1:a10) "JethroUK©" wrote: tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
|
#12
|
|||
|
|||
|
do you think they'll take my rusty ol' 2002 as a trade in - only used it 3
times - it's still works like new - he he he "Dave Peterson" wrote in message ... You could get that expensive patch named Office 2003 <bg. I screwed up with my earlier response. JethroUK wrote: it doesn't work on my works machine (2002 +sp3) is there any update i can get that includes this feature? "Debra Dalgleish" wrote in message ... That feature was introduced in Excel 2003 (but maybe it was in your special version of Excel 2002 <g). Dave Peterson wrote: If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced =subtotal() function: =subtotal(109,a1:a10) "JethroUK©" wrote: tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
|
#13
|
|||
|
|||
|
Blimey, you're doing a great selling job on that ... my rusty ol' 2002 ...,
how van anyone resist :-) "JethroUK©" wrote in message ... do you think they'll take my rusty ol' 2002 as a trade in - only used it 3 times - it's still works like new - he he he "Dave Peterson" wrote in message ... You could get that expensive patch named Office 2003 <bg. I screwed up with my earlier response. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtotal function with Filtered Data | Excel Discussion (Misc queries) | |||
| Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
| subtotal - pivot table - or better function | Excel Worksheet Functions | |||
| Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
| How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Linear Mode
