VBA Advanced auto filter for multiple columns containing "y" or "n" - goal is to exclude rows w/ "n" in certain columns

[tdgvba]

Hi, new to VBA and have been having difficulty finding a solution on the following issue and would greatly appreciate any ideas.

Would like to be able to filter the "Data" table based on the following criteria i.e. show all companies that are either "office" or "industrial" or "office" and "industrial."

In other words, the results should exclude/ filter out company "Name 4" with "n" in both "office" and "industrial"

Would prefer not using hide/ unhide rows but not sure if possible to be done with auto filter or advanced filter.

Criteria

[TABLE="width: 500"]
<tbody>[TR]
[TD]Office[/TD]
[TD]Indsutrial[/TD]
[TD]Retail[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]y[/TD]
[TD]n[/TD]
[/TR]
</tbody>[/TABLE]

Data Table

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]Office[/TD]
[TD]Industrial[/TD]
[TD]Retail[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]n[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]n[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]n[/TD]
[TD]n[/TD]
[TD]n[/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD]y[/TD]
[TD]n[/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[Peter_SSs]

Welcome to the MrExcel board!

Seems like you want to do this with vba but it could be done manually with Advanced Filter too.
I'm not sure of your exact layout, but for this task there is no need for the criteria cells that you are showing at the top so I have the main table starting in row 1 as shown.

Book1
	A	B	C	D	E
1	Company Name	Office	Industrial	Retail
2	Name 1	y	y	y
3	Name 2	n	y	y
4	Name 3	y	n	n
5	Name 4	n	n	n
6	Name 5	y	n	y
Adv Filter

I am assuming that column E is available to use for a criteria range (just 2 cells). Any other column could easily be used if E is not available.

Sub AdvFilter()
  Dim rCrit As Range
  
  Set rCrit = Range("E1:E2")
  rCrit.Cells(2).Formula = "=OR(B2=""y"",C2=""y"")"
  Range("A1", Range("D" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
End Sub

My sheet after running the code:

Book1
	A	B	C	D	E
1	Company Name	Office	Industrial	Retail
2	Name 1	y	y	y	TRUE
3	Name 2	n	y	y
4	Name 3	y	n	n
6	Name 5	y	n	y
Adv Filter

Cell Formulas
Range	Formula
E2	=OR(B2="y",C2="y")

 

[tdgvba]

Thank you so much, Peter. Greatly appreciated and is giving me an idea how I could potentially achieve what I am looking for. The reason for the criteria, I should have made it clear, is that I want it to be dynamic i.e be able to change the search to "industrial" only for example or "office" and "retail" or "office", "industrial" and "retail"...

Again, much appreciated.

 

[Peter_SSs]

The reason for the criteria, I should have made it clear, is that I want it to be dynamic i.e be able to change the search to "industrial" only for example or "office" and "retail" or "office", "industrial" and "retail"...

OK, let's try another approach then. First though, are you aware that for criteria set up like this, criteria entered on the same row are AND criteria? If you want an OR condition you need to put the criteria on separate rows. Further information here if you need it. The set up below then is filtering for (Office = y AND Retail = y) OR (Industrial = y)

Book1
	A	B	C	D
1	Company Name	Office	Industrial	Retail
2		y		y
3			y
4
5
6
7	Company Name	Office	Industrial	Retail
8	Name 1	y	y	y
9	Name 2	n	y	y
12	Name 5	y	n	y
13
Adv Filter

Try this Worksheet_Change event code for the layout I have shown. Post back if you cannot adapt to your layout or if you need details of how to implement this event code.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCrit As Range, rData As Range
  
  Set rData = Range("A7").CurrentRegion
  If Not Intersect(Target, Range("A1:D5")) Is Nothing Then
    Set rCrit = Range("A1").CurrentRegion
    If rCrit.Rows.Count = 1 Then
      If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    Else
      rData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End If
  End If
End Sub

 

[tdgvba]

Thank you! Should be able to adopt to my layout. Be well.

 

[Peter_SSs]

Thank you! Should be able to adopt to my layout. Be well.

Cheers.