help with vba and multiple filters

[kfhw720]

Hi,
I have some values in column A which just contain an 'x'. Then I have some values in column C that just contain a 'y'.
I want to have some vba code that will filter all rows that have an 'x' in A OR a 'y' in C.
Is it possible?
James

 

[akmatz]

So, just to be clear, your desired solution is to see rows where Column A contains 'x' AND Column C contains 'y'? Is this correct?

 

[kfhw720]

Hey. No, sorry.
I want to see any row from A that contains an x and any row from C that contains a y.
E.g. some filtered rows may have only an x in A. Some may only have a y in C. Some could have an x in A and a y in C. Hope that makes sense.

 

[akmatz]

Hey. No, sorry.
I want to see any row from A that contains an x and any row from C that contains a y.
E.g. some filtered rows may have only an x in A. Some may only have a y in C. Some could have an x in A and a y in C. Hope that makes sense.

sample data-please confirm

after concatenation and filter - is this the solution set that you want to see?

 

[kfhw720]

Yes. Exactly

 

[kevin9999]

I see you have 365 on your profile. A formula solution could look like this:

Book1
	A	B	C	D	E	F	G
1	HDR1	HDR2	HDR3		HDR1	HDR2	HDR3
2	x	Blah1	y		x	Blah1	y
3		Blah2	y			Blah2	y
4	x	Blah3			x	Blah3
5		Blah4			x	Blah5
6	x	Blah5				Blah6	y
7		Blah6	y		x	Blah7
8	x	Blah7				Blah8	y
9		Blah8	y
10		Blah9
Sheet1

Cell Formulas
Range	Formula
E1:G1	E1	=A1:C1
E2:G8	E2	=FILTER(A2:C10,(A2:A10="x")+(C2:C10="y"),"")&""
Dynamic array formulas.

Alternatively, the following VBA (run on the active sheet)

Option Explicit
Sub X_or_Y()
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
    Dim i As Long
    For i = 2 To Cells.Find("*", , xlFormulas, , 1, 2).Row
        If Cells(i, 1) = "x" Or Cells(i, 3) = "y" Then Rows(i).Hidden = False Else Rows(i).Hidden = True
    Next i
End Sub

Converts this:

Book1
	A	B	C
1	HDR1	HDR2	HDR3
2	x	Blah1	y
3		Blah2	y
4	x	Blah3
5		Blah4
6	x	Blah5
7		Blah6	y
8	x	Blah7
9		Blah8	y
10		Blah9
Sheet2

Into this:

Book1
	A	B	C
1	HDR1	HDR2	HDR3
2	x	Blah1	y
3		Blah2	y
4	x	Blah3
6	x	Blah5
7		Blah6	y
8	x	Blah7
9		Blah8	y
11
Sheet2

 

[kfhw720]

Thank you!!!