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?