1

I am creating a filter based on pre-created customizable criteria sets from another 'filter sheet'. However, I want to then sort these filter in another display sheet based on the criteria set.

Example: In Filter sheet I have the sets A, B and C. A containing the companies X,Y,Z; B containing companies Q,Y,Z and C containing only company X.

Now, in the display sheet, I have a drop down list of sets A,B,C. I want to display in this sheet based on the criteria from the set (if I pick set A, then only companies X,Y,Z are shown).

Here is my current code:

 With Worksheets("Data Sheet")
        With .Range("A2:R" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        
            .AutoFilter Field:=5, criteria1:= Sheet1.Range("C6") 

'Filter Field 5 (Column E) displays all companies and I want the criteria to be set based on the previously mentioned criteria sets (A,B,C). "Sheet1.Range("C6")" is the cell with the dropdown list

Thanks in advance!

Frankfurt Calling
  • 11
  • 1
  • Where are the company names located? Should they be hard-coded into the code? Are your headers in row 1 or row 2? Is there data to the right of column `R`? – VBasic2008 Mar 14 '23 at 09:21
  • Company names can be picked/chosen by the user. They are not hard-coded. Headers are in row 1. There is only data from column A to R. – Frankfurt Calling Mar 14 '23 at 09:30
  • Based on what's selected in the drop down list, you could fill an `arr` containing what's in the selected set and then use that in your `criteria1` while using `Operator:=xlFilterValues` like they do in this [similar question](https://stackoverflow.com/questions/38404176/using-string-array-as-criteria-in-vba-autofilter) – Notus_Panda Mar 14 '23 at 09:51
  • Hey Notus_Panda thanks for the input. I'm figuring out the case if my flters are in another sheet as opposed to having it in the same sheet. – Frankfurt Calling Mar 14 '23 at 10:27

1 Answers1

0

Filter Data By Multiple Strings

Sub FilterByValues()
    
    Dim Criteria
    
    Select Case Sheet1.Range("C6").Value
        Case "A": Criteria = Array("X", "Y", "Z")
        Case "B": Criteria = Array("Q", "Y", "Z")
        Case "C": Criteria = Array("X")
    End Select
    
    If IsEmpty(Criteria) Then Exit Sub
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Sheets("Data Sheet")
    If ws.FilterMode Then ws.ShowAllData
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    Dim rg As Range: Set rg = ws.UsedRange

    rg.AutoFilter 5, Criteria, xlFilterValues

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thanks man, but what if I let the user (with no knowledge of VBA) choose their own criteria in Excel as a column? – Frankfurt Calling Mar 14 '23 at 10:25
  • Where are the company names located? How are they connected to A, B, C? – VBasic2008 Mar 14 '23 at 10:30
  • So basically, A, B, C are the filter sets (in reality I will use names of the person responsible for the companies). The user can choose for which companies a person is responsible for. Company names will be chose manually – Frankfurt Calling Mar 14 '23 at 11:32
  • The columns would look like this: A1: A (person's name), A2: (Company X), A3: (Company Y), A4: (Company Z) and so on – Frankfurt Calling Mar 14 '23 at 11:33
  • Using the filter on the display/data sheet, I will choose in Sheet1.Range("C6") the person's name (A, B, or C or maybe even more) and I want it to display the list of all companies from that filter set in the sheet – Frankfurt Calling Mar 14 '23 at 11:35