Autofilter on cell value

jakeman

Active Member
Joined
Apr 29, 2008
Messages
313
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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,556
Members
414,155
Latest member
Grainne whiteside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top