tried using SUBTOTAL function (XL XP) and it doesn't seem to work

  • Thread starter Thread starter JethroUK©
  • Start date Start date
J

JethroUK©

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?
 
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)
 
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
 
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)
 
If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced
=subtotal() function:

=subtotal(109,a1:a10)
 
That feature was introduced in Excel 2003 (but maybe it was in your
special version of Excel 2002 <g>).
 
it doesn't work on my works machine (2002 +sp3)

is there any update i can get that includes this feature?
 
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)
 
No, unfortunately the 100 series arguments for Subtotal aren't available
for Excel 2002.
 
You could get that expensive patch named Office 2003 <bg>.

I screwed up with my earlier response.
 
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
 
Blimey, you're doing a great selling job on that ... my rusty ol' 2002 ...,
how van anyone resist :-)
 
Back
Top