VBA to filter columns and rows based on drop-down

[Aaronawl]

Picture of spreadsheet:

https://prnt.sc/ly46pc

Hi all,

I Would like to be able to filter the data shown based on a dropdown in D5.

The 3 options are the teams: Estate Planner, Advisor Team, Review Team (employee names have been blanked out for the image)

When a team is selected in D5 I would like only the columns E to X to show the relevant employees that are part of that team, At the moment I have got this to work using the code:

 Dim rCell As Range For Each rCell In Range("E9:X9")
 rCell.EntireColumn.Hidden = False
 Next rCell
 For Each rCell In Range("E9:X9")
 rCell.EntireColumn.Hidden = (rCell.Value <> Range("D5").Value)
 Next rCell

But I would also like the same code the filter columns A:C and hide rows 11:234 based on if the team specified in D5 appears in any of the range A11:C234 (currently the small unreadable text in A11:C234 are the teams)

There may be a better way to have this option (I would have liked to use Pivot table Slicers rather than a dropdown - as potentially this could select 2 teams data to be shown. Any help or suggestions are appreciated.

Thanks

 

[Michael M]

The link you provided doesn't work....can you have another look at it please.

 

[Aaronawl]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3
4
5				Review Team
6
7
8				Employee Name:	Employee1	Employee2	Employee3	Employee4	Employee5	Employee6	Employee7	Employee8	Employee9	Employee10	Employee11	Employee12	Employee13	Employee14	Employee15	Employee16	Employee17	Employee18	Employee19	Employee20
9				Team:	Estate Planner	Estate Planner	Advisor Team	Review Team	Advisor Team	Advisor Team	Review Team	Estate Planner	Advisor Team	Advisor Team	Advisor Team	Advisor Team	Review Team	Estate Planner	Advisor Team	Estate Planner	Advisor Team	Review Team	Review Team	Estate Planner
10
11	Advisor Team	Review Team	Estate Planner	Fact Finds (2nds)
12	Advisor Team	Review Team	Estate Planner	Jan 19
13	Advisor Team	Review Team	Estate Planner	Feb 19
14	Advisor Team	Review Team	Estate Planner	Mar 19
15	Advisor Team	Review Team	Estate Planner	Apr 19
16	Advisor Team	Review Team	Estate Planner	May 19
17	Advisor Team	Review Team	Estate Planner	Jun 19
18	Advisor Team	Review Team	Estate Planner	Jul 19
19	Advisor Team	Review Team	Estate Planner	Aug 19
20	Advisor Team	Review Team	Estate Planner	Sep 19
21	Advisor Team	Review Team	Estate Planner	Oct 19
22	Advisor Team	Review Team	Estate Planner	Nov 19
23	Advisor Team	Review Team	Estate Planner	Dec 19
24	Advisor Team	Review Team	Estate Planner	Total
25
26	Advisor Team	Review Team	Estate Planner	Presentations (3rds)
27	Advisor Team	Review Team	Estate Planner	Jan 19
28	Advisor Team	Review Team	Estate Planner	Feb 19
29	Advisor Team	Review Team	Estate Planner	Mar 19
30	Advisor Team	Review Team	Estate Planner	Apr 19
31	Advisor Team	Review Team	Estate Planner	May 19
32	Advisor Team	Review Team	Estate Planner	Jun 19
33	Advisor Team	Review Team	Estate Planner	Jul 19
34	Advisor Team	Review Team	Estate Planner	Aug 19
35	Advisor Team	Review Team	Estate Planner	Sep 19
36	Advisor Team	Review Team	Estate Planner	Oct 19
37	Advisor Team	Review Team	Estate Planner	Nov 19
38	Advisor Team	Review Team	Estate Planner	Dec 19
39	Advisor Team	Review Team	Estate Planner	Total
40
41		Review Team		4ths Attended
42		Review Team		Jan 19
43		Review Team		Feb 19
44		Review Team		Mar 19
45		Review Team		Apr 19
46		Review Team		May 19
47		Review Team		Jun 19
48		Review Team		Jul 19
49		Review Team		Aug 19
50		Review Team		Sep 19
51		Review Team		Oct 19
52		Review Team		Nov 19
53		Review Team		Dec 19
54		Review Team		Total
55
56		Review Team		5ths Attended
57		Review Team		Jan 19
58		Review Team		Feb 19
59		Review Team		Mar 19
60		Review Team		Apr 19
61		Review Team		May 19
62		Review Team		Jun 19
63		Review Team		Jul 19
64		Review Team		Aug 19
65		Review Team		Sep 19
66		Review Team		Oct 19
67		Review Team		Nov 19
68		Review Team		Dec 19
Report

 

[Aaronawl]

I have used MrExcel HTML Maker to post the main bulk of the spreadsheet. I hope this helps

https://prnt.sc/ly46pc

 

[Michael M]

This will hide the affected columns, but I don't follow the question regarding the rows !!
They ALL appear to be required by the "review team"

Sub MM1()
 Dim lc As Integer, c As Integer
 lc = Cells(8, Columns.Count).End(xlToLeft).Column
 For c = lc To 5 Step -1
    If Cells(9, c).Value <> Cells(5, 4).Value Then
        Columns(c).EntireColumn.Hidden = True
    End If
 Next c
End Sub

 

[Aaronawl]

Sorry, it wasn't the best description.

All data on the screen is required by the Review Team, but the data continues down to row 234 - some of which isnt required when the Review Team is selected in cell D5 drop-down.

If cell D5 is changed to Advisor Team, I dont need to see the current data 4ths or 5ths attended.

Essentially, I need the VBA to filter the columns and the rows. Only showing data relevant to the team selected in D5.

Hopefully this is clearer, thank you for your help thus far