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

[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?

 

[Bob Phillips]

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©]

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]

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)

 

[Dave Peterson]

If you've hidden rows manually in xl2002 or xl2003, you can use the enhanced
=subtotal() function:

=subtotal(109,a1:a10)

 

[Debra Dalgleish]

That feature was introduced in Excel 2003 (but maybe it was in your
special version of Excel 2002 <g>).

 

[Dave Peterson]

Oops. You're correct. I forgot.

Sorry, ma'am.

 

[JethroUK]

it doesn't work on my works machine (2002 +sp3)

is there any update i can get that includes this feature?

 

[JethroUK]

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 )

 

[Debra Dalgleish]

No, unfortunately the 100 series arguments for Subtotal aren't available
for Excel 2002.

 

[Dave Peterson]

You could get that expensive patch named Office 2003 <bg>.

I screwed up with my earlier response.

 

[JethroUK©]

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

 

[Bob Phillips]

Blimey, you're doing a great selling job on that ... my rusty ol' 2002 ...,
how van anyone resist