Solved Autofilter Column of Data Based on Cell Input

Microsoft Office excel 2003
July 30, 2014 at 03:47:01
Specs: Windows XP
Hi,

I have a table of data which I would like to filter based on a manual cell input instead of the autofilter option.

The table is 10 columns wide, with a series of numbers in column F. I would like to filter the data for all data greater than or equal to a cell input corresponding to column F.

I have experience recording macros and can interpret VBA code but my ability to write code is very low.


See More: Autofilter Column of Data Based on Cell Input

Report •

#1
July 30, 2014 at 06:09:08
I'm a little confused by your statement: "I would like to filter the data for all data greater than or equal to a cell input corresponding to column F."

Do you mean that you are changing a value in Column F and you want the Filter to be applied once the change is made or are you changing some other cell (i.e. outside of the table) and you want the table filtered on Column F based on that external cell?

Some actual range references to the table and cells involved would be helpful.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 30, 2014 at 06:17:53
Yes to the second part of your statement.

For example, F7:F3000 is a column filled with individual numbers in each cell.

I would like to type 0.30 in cell F2, and filter for all values greater than or equal to 0.30 in the column. If I could then delete the value in cell F2 and the column's filter would reset, that would be awesome.

message edited by AJKim


Report •

#3
July 30, 2014 at 07:44:09
✔ Best Answer
I am assuming that you have Column Headings for your table in Row 6, with data in F7:F3000.

If that is true, try this...

1 - Right-Click the sheet tab for the sheet that holds your table
2 - Select View Code
3 - Paste this code into the pane that opens
4 - Enter a value in F2

Private Sub Worksheet_Change(ByVal Target As Range)
'Autofilter Column F based on value in F2, Criteria ">="
   If Target.Address = "$F$2" Then
       ActiveSheet.Range("$F$6:$F$3000").AutoFilter _
               Field:=1, Criteria1:=">=" & Range("F2")
   End If
End Sub

Please be aware that each time this code runs, your "undo" history will be erased. That happens anytime you run a macro.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 30, 2014 at 08:00:44
Thanks this is great! Is there any way, after running this macro, that I can run another macro to bring it back to the original table? Or do anything at all to bring back the original table?

Report •

#5
July 30, 2014 at 09:15:48
A few options that come to mind:

1 - Assuming all values in F7:F3000 are >= 0, enter 0 in F2. The AutoFilter will remain on, you'll simply be Filtering on >=0

2 - Click Data...Filter to turn off the AutoFilter. It will turn back on the next time the macro runs, i.e. the next time F2 is changed.

3 - Assign this macro to a button to toggle the AutoFilter:

Sub ToggleAutoFilter()
 ActiveSheet.Range("$F$6:$F$3000").AutoFilter
End Sub

That code will toggle AutoFilter On or Off depending on its current state. The Worksheet_Change code will still produce the filtered results when F2 is changed.

Using .AutoFilter on a range without specifying any Criteria will just toggle it on or off. If a Criteria is supplied in the code, then it forces the AutoFilter to turn on and filters the data. That's why the Worksheet_Change macro will work regardless of the current state of the AutoFilter.

4 - Use the following code instead and then delete the value in F2 to turn off AutoFilter automatically. Note: With this option, deleting the value in F2 and/or entering 0 in F2 will both result in the entire table showing (see Option 1) but only Deleting the value will actually turn off the AutoFilter.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if F2 has been changed
   If Target.Address = "$F$2" Then
'Determine if F2 contains a value
     If Target.Value <> "" Then
'If TRUE, Autofilter Column F based on value in F2, Criteria ">="
       ActiveSheet.Range("$F$6:$F$3000").AutoFilter _
                   Field:=1, Criteria1:=">=" & Range("F2")
'If FALSE, turn off AutoFilter
     Else: ActiveSheet.Range("$F$6:$F$3000").AutoFilter
     End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question