Hello all, just trying to sort out a macro contained on an Excel sheet which filters a table range.
The table (containing names and several other details) is presented to the user in raw, unfiltered format. The user selects from between 1-6 data validation boxes and - when running the macro - VBA will take the values in the boxes and apply a filter to include the desired values. This works successfully using the below code and means the table is filtered to what they want to see:
Worksheets("Consultant List").Select
ActiveSheet.Range("A9:H300").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria1:=Split(WorksheetFunction.TextJoin(",", True, Range("E1:E6")), ",")
The only other filter required is the one I cannot figure out how to code! It needs to run on the same range at the same time. Quite simply, it's a second filter (on a separate column) which the user can apply by choosing from a data validation cell. The idea is that they would select a value and when the macro is ran, VBA should exclude the value specified. The code I've tried (and failed with) is:
ActiveSheet.Range("A9:H300").AutoFilter Field:=5, Criteria1:=("<>G5")
I've tried recording the macro, but this results in every other autofilter being listed which doesn't work. I've made sure the data is formatted correctly and that the values in the data validation list are taken from an extract of a column in the table.
Hoping somebody can help!
Bookmarks