Small macro to aid filtering 1

[henio]

Hi all,

I have a worksheet to which I need to apply the same filters to various columns to extract totals. Some of the filters can become quite complex, so I recorded some macros to simplify my life (and reduce keying errors).

An very simple example is shown below.

Sub Macro1()

Selection.AutoFilter Field:=13, Criteria1:="80", Operator:= _
xlBottom10Percent
End Sub

My problem is that I want to be able to use this macro on any column, not just 13. I tried adding activecell.select, but I think I need to replace the Field:=13 with something.

As always, any advice would be greatly appreciated.

Henio

 

[TonyJollans]

Hi henio,

It depends a little on exactly how you want to work, but to apply your filter based on the activecell you could apply the filter to the first column in the range which is the column the active cell is in:

ActiveCell.EntireColumn

.AutoFilter Field:=

1

, Criteria1:="34", Operator:=xlBottom10Percent

Enjoy,
Tony

-------------------------------------------------------------------
Wishing all Tek-Tippers a Happy and Prosperous New Year

 

[DrBowes]

Or

Sub Macro1()
Cells.AutoFilter _
Field:=activecell.column, _
Criteria1:="80", Operator:= xlBottom10Percent
End Sub

 

[henio]

DrBowes,

a star for this together with the inevitable follow-up question! I attached this to a button on the toolbar. I'd like to be able to toggle between setting the criteria and clearing them. Any ideas?

Cheers,
Henio

 

[xlbo]

Cells.autofilter
should clear any set filters

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244