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?

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©" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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©" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> >
>
>

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©" <[email protected]> wrote in message
news:Y9NNe.1748$%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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©" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >
> > >
> >
> >
>
>

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

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

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

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

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

"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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©" <[email protected]> wrote in message
> news:Y9NNe.1748$%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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©" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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?
>> > >
>> > >
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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

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



"JethroUK©" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > You could get that expensive patch named Office 2003 <bg>.
> >
> > I screwed up with my earlier response.
> >