Selected Answer
Beepee
Thanks for attaching the file. Before I Answer, please note that is this fixes your problem, please mark the Answer as Selected (in accordance with the Forum Rules).
In the attached revised files, I've replaced your macro-recorded line with the one in bold below (and revised the comments describing the procedure, including a note about how to filter out dates):
Sub HideN()
' To hide rows where column G matches cell K1 - text entries only,
' unless dates are converted to serial number using e.g.=DATEVALUE("05/10/2023")
'
'ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7, Criteria1:= _
Array("Later", "NR", "NS", "="), Operator:=xlFilterValues, Criteria2:=Array(0, _
"10/5/2023")
' ## replaced macro recorder line above with...
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7, Criteria1:="<>" & Range("K1").Value
End Sub
If you put N in (yellow) cell K1 and press the purple button (near row 1 column J) labelled "Hide entries in G=", all the N entires will be hidden. Change it to NR and press again- they'll be restored but NR entries will be hidden.
To clear the filter altogether, click the green button "Clear filter from table" (to the right of K1, which is assigned to this code:
Sub ClearFilter()
' To clear filter from table
If ActiveSheet.ListObjects("Table1").Range.AutoFilter = True Then ActiveSheet.ListObjects("Table1").Range.AutoFilter
End Sub
Revision 25 October 2023:
This addresses the (replacement) file attached to the question.
The current button ""Display All" invokes the code below, modified as follows (see changes in bold):
Private Sub AlmanacClearFilter() 'P226 clear filters to Display All
'Origin: 1 Jul 2023 (C)BeePee From: Paul Kelly Webinar - excelmacromastery.com/vba-advanced-filter/
'called via IndicateShape, from click-buttom "Display All" (Almanac w/s)
'vbaGardenProject1(GardenAlmanac.xlsm)
'Module: Almanac
'Version update -- v 1.0 -- 1 Jul 2023
'
' Last saved as word docx Ver 1.0 2023 18:00
'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
'clear Filters
'NB TrialFiltering is code name for almanac w/s
If TrialFiltering.FilterMode = True Then
TrialFiltering.ShowAllData
End If
'select current region
Range("D8").CurrentRegion.Select
Selection.Interior.Color = RGB(225, 240, 220) 'sage green
'reset header colour
Range("B7:I7").Interior.Color = RGB(35, 95, 145) 'dark blue
'''Worksheets("Filter Data").
'Range("B7").AutoFilter ' display filter icons
' ## line above replaced with this to filter out N in column F...
Range("B7").CurrentRegion.AutoFilter Field:=7, Criteria1:="<>N"
Range("D8").Select
End Sub 'endAlmanacClearFilter P226
This displays all lines APART from those with "N" in column F. Accordingly, in the second attached file, I have relabelled that button "Display All (<>N)" (and also corrected the spelling to Commodity in two places).
Hope this fixes your problem. If so, don't forget to mark this Answer and Selected (sorry to repeat but so many new user do forget)
