|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:
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 ">="
Field:=1, Criteria1:=">=" & Range("F2")
'If FALSE, turn off AutoFilter
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.