Autofilter on cell value

[jakeman]

I'm using Excel 2007. I'd like to be able to autofilter on a particular value that I put in cell J5. How can I do that syntactically?


Thanks.

 

[rosenbe]

Hi Jakeman, what do you mean by Autofilter?

 

[jakeman]

I mean filtering in general.

 

[rosenbe]

Try...

Range("J5").Select
Selection.AutoFilter

 

[BigC]

1. Name the range of column headings as "Headings"
2. Name the range holding your variable criteria (J35) as "MyCriteria"
3. Name the cell holding the headings of the field/column you want to filter as "Filterfield"
4. Add this code to a standard module in your workbook:

Sub Filter()
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim c as long

Set r1 = Range("Headings")
Set r2 = Range("FilterField")
Set r3 = Range("MyCriteria")

c = r2.column - r1.column + 1
r1.AutoFilter Field:=c, Criteria1:=r3.Value

End Sub