help with vba and multiple filters

kfhw720

New Member
Joined
May 3, 2024
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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?
 
Upvote 0
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.
 
Upvote 0
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
1714860450576.png


after concatenation and filter - is this the solution set that you want to see?
1714860549314.png
 
Upvote 0
I see you have 365 on your profile. A formula solution could look like this:
Book1
ABCDEFG
1HDR1HDR2HDR3HDR1HDR2HDR3
2xBlah1yxBlah1y
3Blah2yBlah2y
4xBlah3xBlah3
5Blah4xBlah5
6xBlah5Blah6y
7Blah6yxBlah7
8xBlah7Blah8y
9Blah8y
10Blah9
Sheet1
Cell Formulas
RangeFormula
E1:G1E1=A1:C1
E2:G8E2=FILTER(A2:C10,(A2:A10="x")+(C2:C10="y"),"")&""
Dynamic array formulas.


Alternatively, the following VBA (run on the active sheet)
VBA Code:
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
ABC
1HDR1HDR2HDR3
2xBlah1y
3Blah2y
4xBlah3
5Blah4
6xBlah5
7Blah6y
8xBlah7
9Blah8y
10Blah9
Sheet2


Into this:
Book1
ABC
1HDR1HDR2HDR3
2xBlah1y
3Blah2y
4xBlah3
6xBlah5
7Blah6y
8xBlah7
9Blah8y
11
Sheet2
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top