Code to control auto filters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gordon - 20 Jan 2006 15:16 GMT

I have a column that I need auto filtering to the top ten values in say
column/range A10:A100. I'm looking for code to do this. the code should begin
with removing all filters and then re-instating them so they are back to an
unfiltered state. I don't want my users touching the filters, but more just
touching a macro button to execute the procedure. I asked this question
earlier today but the code came up as invalid in red.

Any help gratefully received.

Gordon

Reply to this Message

Zack Barresse - 20 Jan 2006 16:26 GMT

Hello Gordon,

You could use something like this ...

Option Explicit
Sub FilterTopTen()
'take off autofilter
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.Cells.AutoFilter
End If
'set new autofilter
Range("A10:A100").AutoFilter Field:=1, Criteria1:="10",
Operator:=xlTop10Items
' Range("A10:A100").AutoFilter Field:=1, Criteria1:="10",
Operator:=xlTop10Percent
End Sub

You didn't specify if you wanted top 10 items or percent. You have
either/or to choose from. Note this assumes the activesheet is the sheet
with the range you wish to filter.

HTH

Signature

Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.

>I have a column that I need auto filtering to the top ten values in say
> column/range A10:A100. I'm looking for code to do this. the code should
[quoted text clipped - 9 lines]
>
> Gordon

Reply to this Message