Advanced Filter with OR and Multiple Criteria per Column & Combo Box

[rebeubeu]

Hi guys,

I have been using this forum for quite a long time as a 'spectator'. This is my first post.

I have an advanced filter macro in place for my company. It allows us to match our prospective buyers to our signed content sellers by matching their produced content with the demographics/verticals.

Please refer below to see the VBA code that I am using and the filter rows/criteria.


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 3505"]
<colgroup><col><col><col><col><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="7"><col></colgroup><tbody>[TR]
[TD]Region[/TD]
[TD]Country[/TD]
[TD]Publisher[/TD]
[TD]Website[/TD]
[TD]Websites URL[/TD]
[TD]Website Tier[/TD]
[TD]Demographic[/TD]
[TD]Category[/TD]
[TD]ABC1[/TD]
[TD]Dec-24[/TD]
[TD]25 - 49[/TD]
[TD]50+[/TD]
[TD]Auto/Moto[/TD]
[TD]B2B[/TD]
[TD]Beauty[/TD]
[TD]Business/ Banking/ Insurance[/TD]
[TD]Cinema/ TV/ Music[/TD]
[TD]Culture/ Lifestyle[/TD]
[TD]Education[/TD]
[TD]Entertainment/ Buzz[/TD]
[TD]Family/ Motherhood[/TD]
[TD]Fashion[/TD]
[TD]Food/ Beverage[/TD]
[TD]Gaming[/TD]
[TD]Green Development[/TD]
[TD]Health[/TD]
[TD]Hobbies/ Interests[/TD]
[TD]Home/ Deco/ Gardening/ DIY[/TD]
[TD]Info/ 411[/TD]
[TD]Luxury[/TD]
[TD]News Media[/TD]
[TD]Pets[/TD]
[TD]Science[/TD]
[TD]Sport[/TD]
[TD]Technology[/TD]
[TD]Travel/ Adventure[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]




' AdFilter Macro
' Filters Data
'
' Keyboard Shortcut: Option+Cmd+Shift+D
'
Sheet6.Range("B1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("D7:AR8"), CopyToRange:=Range("D10:AR10"), Unique:= _
False
Range("O10").Select
End Sub

And then a small macro to clean the selection and the data retrieved. Both are assigned to buttons.

This has been working amazingly well but there are some challenges that I can't seem to solve. This are the challenges that I can't solve (I am still quite new to excel/vba so apologies in advance for the noob questions):

• I can't fit more than one item per column, i.e., if I want to retrieve all the websites from Italy it's alright, but If I want to retrieve all of the websites from Italy and all of the websites from Spain, with doesn't retrieve any information. Also, is it possible to do it using a combo box selection as a filter.
• When filtering, sometimes I want the doc to retrieve all the websites that match a category or another category (or both), i.e., all the websites that fit into tech and science. Is it possible to do that?

Thank you very much for your help, it is much appreciated. Don't hesitate to PM if you need any more information.

 

[pbornemeier]

Modify your criteria range so it has as many data rows as you need for multiple items per column. In the case you mentioned above you should have CriteriaRange:=Range("D7:AR9") with 'Italy' and 'Spain' in the 'Country' column.
Values in the same column are treated as 'OR'
Values in the same row are in an 'AND' relationship

You can use data validation (drop down) in your criteria range.

This code moves the output down to accommodate a criteria range with a variable number of rows.

Option Explicit

Sub CopyFilteredDataToAnotherSheet()

    Dim wksData As Worksheet
    Dim wksResults As Worksheet
    Dim rngData As Range
    Dim rngCriteria As Range
    Dim rngOutput As Range
    
    Set wksData = Sheet6
    Set wksResults = ActiveSheet
    
    If wksResults.Name = wksData.Name Then
        MsgBox "Start code on the results worksheet"
        GoTo End_Sub
    End If
    
    Set rngData = wksData.Range("B1").CurrentRegion
    Set rngCriteria = wksResults.Range("D7").CurrentRegion
    
    'Output header will start 1 row below the last criteria row
    Set rngOutput = rngCriteria.Offset(rngCriteria.Rows.Count + 1)
    
    rngData.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=rngCriteria, CopyToRange:=rngOutput, Unique:=False
    
End_Sub:
       
    Set rngOutput = Nothing
    Set rngCriteria = Nothing
    Set rngData = Nothing
    Set wksResults = Nothing
    Set wksData = Nothing
    
    
'With a CriteriaRange of A1:F12 the following would return all rows where
'  column A contained XX and B:F contained any value
'   A   B   C   D   E   F
'1  XX

'With a CriteriaRange of A1:F2 the following would return all rows where
'  column A contained XX and B:F contained any value OR
'  column A contained YY and B:F contained any value
'   A   B   C   D   E   F
'1  XX
'2  YY
'3
'If the CriteriaRange was A1:F3, the above would return all rows where
'  column A contained XX and B:F contained any value OR
'  column A contained YY and B:F contained any value OR
'  columns A:F contained any value

'With a CriteriaRange of A1:F4 the following would return all rows where
'  (column A contained XX and B:F contained any value OR
'  column A contained YY and B:F contained any value) OR
'  column D contained AA OR
'  column F contained BB
'   A   B   C   D   E   F
'1  XX
'2  YY
'3              AA
'4                      BB

'With a CriteriaRange of A1:F4 the following would return all rows where
'  (column A contained XX and B:F contained any value OR
'  column A contained YY and B:F contained any value) OR
'  (column B contained AA and C:F contained any value OR
'  column B contained BB and C:F contained any value) OR
'   A   B   C   D   E   F
'1  XX
'2  YY
'3      AA
'4      BB

End Sub