Filter for word in multiple columns (with a macro and/or slicer)

[marshak]

Hello all!

I have a problem that I need solved, and am hoping there is a solution.

PROBLEM: I need to filter for the word "Caputo" in both columns E and F. If I filter column E first, then that will filter out rows that may have "Caputo" in column F.

OPTION 1 (macro): In the current table I created a macro that does what I need it to do (found in columns J and K), however, If i insert a row in the table (for example a row above the A3), my macro no longer works (and the formatting for my macro changes). Can i create a macro that will do what I need, but will not be affected by the additions/deletions of rows and columns to the table?

OPTION 2 (slicer): Can I create a slicer that "sits" on top of the cells, so that no matter what additions and deletions I make in the table -- it will not affect the formatting of the slicer.

Thank you so much in advance!! Please also let me know if I can provide any further explanation.

100478__131356007v5_AEO - Chronology - MACROS ENABLED.XLSM
	A	B	C		E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Date	Event	Source		Primary Relevance	Secondary Relevance	Key Event?			Primary Relevance	Secondary Relevance
2	1995				Caputo					Caputo
3	1994-1997				Caputo						Caputo
4	1997-2002				Caputo
5	June 1997-Dec. 2002				Caputo					Key Event?
6	2000				USRC Background	Caputo				Y
7	2002				Caputo	Caputo
8	8/27/2003				Dorsey Connections	Caputo
9	2007
10	5/30/2007				Dorsey Connections	Caputo
11	6/22/2007				Dorsey Connections	Caputo
12	7/17/2008				Dorsey Connections	Caputo
13	5/7/2009				Dorsey Connections	Caputo
14	6/19/2009				Dorsey Connections	Caputo
15	12/4/2009				Dorsey Connections	Caputo
16	12/4/2009				Caputo	Caputo
17	12/21/2009				MDAs		Y
18	12/21/2009				MDAs		Y
19	12/21/2009				MDAs		Y
20	1/6/2010				MDAs		Y
21	Approx. April 2010				Caputo
22	7/6/2010				Dorsey Connections	Caputo
23	8/3/2010				Dorsey Connections	Caputo
24	8/16/2010				TMs
25	11/12/2010				Dorsey Connections
26	12/17/2010				Caputo
27	1/24/2011				MDAs		Y
28	3/17/2011				Dorsey Connections
29	8/2/2011				Caputo
30	1/31/2012				Dorsey Connections
31	2/28/2012				Caputo
32	4/16/2012				MDAs
33	5/21/2012				MDAs		Y
Master Chron

Cells with Data Validation
Cell	Allow	Criteria
F6:F33	List	=Relevance_List

 

[kevin9999]

I'm not clear about whether you want to include rows with Caputo in column E or not. Here's 2 options. Option A includes Caputo in column E and excludes Caputo in column F. Option B excludes Caputo in both columns E and F. Neither macro is affected when you insert a row.

Sub marshak_A()
    With Worksheets("Master Chron").Range("E:F")
        .AutoFilter 1, "Caputo"
        .AutoFilter 2, "<>Caputo"
    End With
End Sub

Sub marshak_B()
    With Worksheets("Master Chron").Range("E:F")
        .AutoFilter 1, "<>Caputo"
        .AutoFilter 2, "<>Caputo"
    End With
End Sub

 

[marshak]

Thanks so much! I'd like to include rows with Caputo in both Columns E and F. Can you please send me that macro? Thanks so much!!

 

[kevin9999]

Option C then

Sub marshak_C()
    With Worksheets("Master Chron").Range("E:F")
        .AutoFilter 1, "Caputo"
        .AutoFilter 2, "Caputo"
    End With
End Sub

 

[marshak]

Thanks so much for your time. I tried entering the created macro and this is the error message that I get. What am I doing incorrectly?

 

[kevin9999]

See if this one makes any difference:

Sub marshak_D()
    With Worksheets("Master Chron").UsedRange
        .AutoFilter 5, "Caputo"
        .AutoFilter 6, "Caputo"
    End With
End Sub