Macro needed to filter a range

[cra5hdown]

Have a look at the attached.

Essentially, i am unable to house in a table due to spec. and need to filter based on two inputs.

Range is Check_table - A17:H163
Filter 1: Cell H9, the concept. based on a choice here the Range needs to be filter on Col B.
for example, if 'n' is chosen, filter out (hide) all rows in the range where 'n' does not appear in the Col, the chosen letter could be within 3 letters, eg 'ent'

Filter 2: Cell E10, the type. based on a choice here the Range needs to be filtered further, in the same way, on Col C. (D - detailed, C - close, V - visual)

For both filters, the option 'Unspecified' should show all rows.
The order should always be the Col 1 of Range, the Ref.
Though Col 4 may look repetitive, the refs (eg 'A01') are unique based on Concept. (more detail will be added later)
Sheet Cols ABC will be hidden later

Thank you for your assistance.
Excel 365

HAI.xlsx
	A	B	C	D	E	F	G	H
1
2
3
4
5
6
7
8
9							Concept	i
10				Type	Detailed
11
12
13
14				Comments:
15
16	Hidden
17	Ref	Concept	Type	Check Completed:				Result
18	1	ent	DCV	SECTION A: GENERAL - ALL EQUIPMENT
19	2	ent	DCV	A01
20	3	ent	DC	A02
21	4	ent	DC	A03
22	5	ent	DC	A04
23	6	ent	DCV	A05
24	7	ent	D	A06
25	8	ent	DCV	A07
26	9	ent	DCV	A08
27	10	ent	D	A09
28	11	ent	CV	A10
29	12	ent	DCV	A11
30	13	ent	D	A14
31	14	ent	D	A15
32	15	ent	D	A17
33	16	ent	D	A18
34	17	ent	D	A19
35	18	ent	D	A20
36	19	ent	D	A21
37	20	ent	D	A22
38	21	ent	D	A23
39	22	ent	D	A24
40	23	ent	DC	A25
41	24	ent	DCV	A26
42	25	ent	DCV	A27
43	26	ent	D	A28
44	27	ent	DCV	A29
45	28	ent	DCV	A30
46	29	ent	D	A31
47	30	ent	DCV	SECTION B: INSTALLATION
48	31	ent	D	B01
49	32	ent	DCV	B02
50	33	ent	DCV	B03
51	34	ent	D	B05
52	35	ent	DCV	B06
53	36	ent	D	B07
54	37	ent	D	B08
55	38	ent	D	B09
56	39	ent	D	B10
57	40	ent	D	B11
58	41	ent	DC	B13
59	42	ent	D	B23
60	43	ent	DCV	SECTION C: ENVIRONMENT
61	44	ent	DCV	C01
62	45	ent	DCV	C02
63	46	ent	D	C03
64	47	in	DCV	SECTION A: GENERAL - ALL EQUIPMENT
65	48	in	DCV	A01
66	49	in	DC	A02
67	50	in	DC	A03
68	51	in	DC	A04
69	52	in	DC	A05
70	53	in	DC	A06
71	54	in	DC	A07
72	55	in	DC	A08
73	56	in	D	A09
74	57	in	DCV	A10
75	58	in	D	A11
76	59	in	CV	A12
77	60	in	DCV	A13
78	61	in	D	A14
79	62	in	D	A15
80	63	in	D	A16
81	64	in	DC	A17
82	65	in	DCV	SECTION B: INSTALLATION
83	66	in	D	B01
84	67	in	D	B02
85	68	in	DCV	B03
86	69	in	DCV	B04
87	70	in	D	B05
88	71	in	D	B06
89	72	in	D	B07
90	73	in	D	B08
91	74	in	D	B09
92	75	in	D	B10
93	76	in	D	B11
94	77	in	D	B12
95	78	in	D	B13
96	79	in	DCV	SECTION C: ENVIRONMENT
97	80	in	DCV	C01
98	81	in	DCV	C02
99	82	d	DCV	SECTION A: GENERAL - ALL EQUIPMENT
100	83	d	DCV	A01
101	84	d	DC	A02
102	85	d	DCV	A05
103	86	d	D	A06
104	87	d	DCV	A07
105	88	d	DCV	A08
106	89	d	D	A09
107	90	d	CV	A10
108	91	d	DCV	A11
109	92	d	DCV	A12
110	93	d	D	A13
111	94	d	D	A14
112	95	d	D	A15
113	96	d	DC	A16
114	97	d	DC	A25
115	98	d	DCV	A27
116	99	d	D	A28
117	100	d	DCV	A29
118	101	d	DCV	A30
119	102	d	D	A31
120	103	d	DCV	SECTION B: INSTALLATION
121	104	d	D	B01
122	105	d	DCV	B02
123	106	d	DCV	B03
124	107	d	D	B04
125	108	d	D	B05
126	109	d	DCV	B06
127	110	d	D	B07
128	111	d	D	B08
129	112	d	D	B09
130	113	d	D	B10
131	114	d	D	B11
132	115	d	DCV	B12
133	116	d	DC	B13
134	117	d	DCV	SECTION C: ENVIRONMENT
135	118	d	DCV	C01
136	119	d	DCV	C02
137	120	d	DCV	SECTION A: GENERAL - ALL EQUIPMENT
138	121	d	DCV	A01
139	122	p	DC	A02
140	123	p	DC	A03
141	124	p	D	A04
142	125	p	DCV	A05
143	126	p	DCV	A06
144	127	p	D	A07
145	128	p	CV	A08
146	129	p	D	A09
147	130	p	DCV	SECTION B: INSTALLATION
148	131	p	D	B01
149	132	p	DCV	B02
150	133	p	DCV	B03
151	134	p	D	B04
152	135	p	D	B05
153	136	p	D	B06
154	137	p	D	B07
155	138	p	DCV	B08
156	139	p	DCV	B09
157	140	p	DCV	B10
158	141	p	D	B11
159	142	p	D	B12
160	143	p	D	B13
161	144	p	DCV	SECTION C: ENVIRONMENT
162	145	p	DCV	C01
163	146	p	DCV	C02
Main

Cells with Data Validation
Cell	Allow	Criteria
E10	List	Initial, Visual, Close, Detailed, Unspecified
H9	List	h,d,e,i,m,n,p,s,t,Uncertified Equipment, Unspecified
H19:H46	List	Fail, Limitation, N/A, N/C, Pass
H48:H51	List	Fail, Limitation, N/A, N/C, Pass
H65:H81	List	Fail, Limitation, N/A, N/C, Pass
H83:H95	List	Fail, Limitation, N/A, N/C, Pass
H97:H98	List	Fail, Limitation, N/A, N/C, Pass
H135:H136	List	Fail, Limitation, N/A, N/C, Pass
H138:H146	List	Fail, Limitation, N/A, N/C, Pass
H148:H160	List	Fail, Limitation, N/A, N/C, Pass
H162:H163	List	Fail, Limitation, N/A, N/C, Pass
H100:H119	List	Fail, Limitation, N/A, N/C, Pass
H121:H133	List	Fail, Limitation, N/A, N/C, Pass

 

[kevin9999]

See if this comes close to what you're looking for.

Option Explicit
Option Compare Text
Sub cra5hdown()
    Dim ws As Worksheet
    Set ws = Worksheets("Main")
    Dim r As Range
    Set r = ws.Range("A17:H" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
   
    Dim s1 As String, s2 As String
    If ws.Range("H9") = "unspecified" Then s1 = "<>" Else s1 = "*" & ws.Range("H9") & "*"
    If ws.Range("E10") = "unspecified" Then s2 = "<>" Else s2 = "*" & Left(ws.Range("E10"), 1) & "*"
   
    With r
        .AutoFilter 2, s1
        .AutoFilter 3, s2
    End With

End Sub

 

[cra5hdown]

See if this comes close to what you're looking for.

Option Explicit
Option Compare Text
Sub cra5hdown()
    Dim ws As Worksheet
    Set ws = Worksheets("Main")
    Dim r As Range
    Set r = ws.Range("A17:H" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
  
    Dim s1 As String, s2 As String
    If ws.Range("H9") = "unspecified" Then s1 = "<>" Else s1 = "*" & ws.Range("H9") & "*"
    If ws.Range("E10") = "unspecified" Then s2 = "<>" Else s2 = "*" & Left(ws.Range("E10"), 1) & "*"
  
    With r
        .AutoFilter 2, s1
        .AutoFilter 3, s2
    End With

End Sub

It doesnt automatically filter based on selections in H9 & E10, however, it does do what im looking for enough!

Thank you!

 

[kevin9999]

I didn't see in your original post where you said you wanted it to filter "automatically", however, if that's what you were looking for, then the following Sheet code should give you what you want

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("H9,E10"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim r As Range
            Set r = Range("A17:H" & Cells(Rows.Count, "A").End(xlUp).Row)
            
            Dim s1 As String, s2 As String
            If Range("H9") = "unspecified" Then s1 = "<>" Else s1 = "*" & Range("H9") & "*"
            If Range("E10") = "unspecified" Then s2 = "<>" Else s2 = "*" & Left(Range("E10"), 1) & "*"
            
            With r
                .AutoFilter 2, s1
                .AutoFilter 3, s2
            End With
        Application.EnableEvents = True
    End If
End Sub

 

[cra5hdown]

I didn't see in your original post where you said you wanted it to filter "automatically", however, if that's what you were looking for, then the following Sheet code should give you what you want

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("H9,E10"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim r As Range
            Set r = Range("A17:H" & Cells(Rows.Count, "A").End(xlUp).Row)
           
            Dim s1 As String, s2 As String
            If Range("H9") = "unspecified" Then s1 = "<>" Else s1 = "*" & Range("H9") & "*"
            If Range("E10") = "unspecified" Then s2 = "<>" Else s2 = "*" & Left(Range("E10"), 1) & "*"
           
            With r
                .AutoFilter 2, s1
                .AutoFilter 3, s2
            End With
        Application.EnableEvents = True
    End If
End Sub

i cant get that one to work.
However, the initial solution is probably better suited as it is for others to use - it does the job.

 

[kevin9999]

i cant get that one to work.
However, the initial solution is probably better suited as it is for others to use - it does the job.

Fair enough, but it's probably where the code is placed. With sheet event code, you right click the tab name, select View Code, and place the code in the windows that appears on the right of the screen. It doesn't go in a standard module.
That aside, I'm glad it worked out for you and thanks for the feedback