FILTER MORE THAN 2 CRITERIA WITH VBA.

Jungo · May 30, 2022

IM LOOKING TO FILTER A COLUMN THAT HAS A LIMITLESS LIST OF POSSIBILITIES. THAT COLUMN HOWEVER ALWAYS HAS 5 ITEMS THAT I NEED FILTERED.

I NEED TO KEEP "HEADER", "BLOCKING", "SILL", "CRIPPLE", "STUD" AND DELETE THE REST.

THIS IS WHAT I HAVE SO FAR. BUT IT DOESNT WORK WHEN I ADD ADDITIONAL CRITERIA, AND ARRAY FOR 5 DIFFERENT ITEMS ISNT WORKING OUT FOR ME EITHER.

SOME HELP PLEASE?

Sub Macro5()
'
' FILTER OUT HEADER, BLOCKING, SILL, CRIPPLE, STUD, AND DELETE THE REST
'

'
Range("A1").Select
Range("A1:I10000").Select
Selection.AutoFilter
Range("A1").Select
ActiveSheet.Range("$A$1:$I$10000").AutoFilter Field:=2, Criteria1:= _
"=*HEADER*", Operator:=xlOr, Criteria2:="=*BLOCKING*"
End Sub

Takae · May 30, 2022

Hope this helps.

VBA Code:

Sub sample1()
Dim x, c, LR As Long
    LR = Cells(Rows.count, 1).End(xlUp).Row
    ' FILTER OUT HEADER, BLOCKING, SILL, CRIPPLE, STUD, AND DELETE THE REST
    x = Application.Transpose(Range(Range("B2"), Cells(LR, 2)).Value)
    For Each c In Array("HEADER", "BLOCKING", "SILL", "CRIPPLE", "STUD")
        x = Filter(x, c, False)
    Next

  Range("A1").AutoFilter 2, x, xlFilterValues
  If WorksheetFunction.Subtotal(3, Range("B:B")) > 1 Then
    Range(Range("B2"), Cells(LR, 2)).EntireRow.Delete
  End If
  Range("A1").AutoFilter
End Sub

kevin9999 · May 31, 2022

Another approach - run from the active sheet.

VBA Code:

Option Explicit
Sub Exclude_5()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim LR As Long, r As Range, arrIn, arrOut, i As Long
    LR = ws.Cells(Rows.Count, 2).End(3).Row

    arrIn = ws.Range("B2:B" & LR).Value
    arrIn = Application.Transpose(Application.Index(arrIn, 0, 1))
    ReDim arrOut(1 To UBound(arrIn))
    
    For i = LBound(arrIn) To UBound(arrIn)
        arrIn(i) = CStr(arrIn(i))
    Next i
    
    For i = LBound(arrIn) To UBound(arrIn)
        Select Case arrIn(i)
            Case Is = "HEADER", "BLOCKING", "SILL", "CRIPPLE", "STUD"
            Case Else
                arrOut(i) = arrIn(i)
        End Select
    Next i

    With ws.Range("A1").CurrentRegion
         .AutoFilter 2, Array(arrOut), 7
         .Offset(1).EntireRow.Delete
         .AutoFilter
    End With
End Sub

Peter_SSs · May 31, 2022

Jungo said:
ActiveSheet.Range("$A$1:$I$10000").AutoFilter Field:=2, Criteria1:= _
"=*HEADER*", Operator:=xlOr, Criteria2:="=*BLOCKING*"

Can you confirm whether the asterisks in your attempted code indicates that column B might contain additional words in a cell as well as one of the words of interest?
That is, might a row that is to be kept contain "NO BLOCKING TODAY" or would it be just the word "BLOCKING" by itself?

Jungo · Jun 2, 2022

kevin9999 said:

Another approach - run from the active sheet.

VBA Code:

Option Explicit
Sub Exclude_5()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim LR As Long, r As Range, arrIn, arrOut, i As Long
    LR = ws.Cells(Rows.Count, 2).End(3).Row

    arrIn = ws.Range("B2:B" & LR).Value
    arrIn = Application.Transpose(Application.Index(arrIn, 0, 1))
    ReDim arrOut(1 To UBound(arrIn))
   
    For i = LBound(arrIn) To UBound(arrIn)
        arrIn(i) = CStr(arrIn(i))
    Next i
   
    For i = LBound(arrIn) To UBound(arrIn)
        Select Case arrIn(i)
            Case Is = "HEADER", "BLOCKING", "SILL", "CRIPPLE", "STUD"
            Case Else
                arrOut(i) = arrIn(i)
        End Select
    Next i

    With ws.Range("A1").CurrentRegion
         .AutoFilter 2, Array(arrOut), 7
         .Offset(1).EntireRow.Delete
         .AutoFilter
    End With
End Sub

Hey Kevin, thanks for the reply.

Two things that keep it from working:
1. the terms "header", "blocking" etc are not exclusive to the cell.. sometimes it might say header #4 or blocking #28. so would a lookup or a "contains" filter work?
2. there is 2 sets of data, so the filter isnt going to the bottom of all the data, just the first break in data.

ill attach the excel file if i can....

So I Downloaded the minisheet uploader, it worked for at moment, and now its not... anywho.. here is the data set below.

i want to keep data the has "header" "blocking" "sill" "cripple" "stud" in column B, and delete the rest of the rows. but as you can see their are multiple header, etc. see below.

let me know if this works

ASH5- ANDERSON SAGE HILLS BLDG 5000 - COMBINED_CutList.xlsx

A

B

1

Quantity

Description

2

HEADER - DOOR

3

2

HEADER - DOOR

4

2

HEADER - DOOR

5

4

HEADER - DOOR

6

2

HEADER - DOOR

7

2

HEADER - DOOR

8

4

HEADER - DOOR

9

2

HEADER - DOOR

10

2

HEADER - DOOR

11

2

HEADER - DOOR

12

2

HEADER - DOOR

13

2

HEADER - DOOR

14

2

HEADER - DOOR

15

2

HEADER - DOOR

16

2

HEADER - DOOR

17

2

HEADER - DOOR

18

12

HEADER - DOOR

19

2

HEADER - DOOR

20

2

HEADER - DOOR

21

2

HEADER - DOOR

22

2

HEADER - DOOR

23

2

HEADER - DOOR

24

2

HEADER - DOOR

25

4

HEADER - DOOR

26

4

HEADER - DOOR

27

2

HEADER - DOOR

28

2

HEADER - DOOR

29

2

HEADER - DOOR#2

30

1

HEADER - SHIM DOOR

31

4

HEADER - SHIM DOOR

32

1

HEADER - SHIM DOOR

33

1

HEADER - SHIM DOOR

34

2

HEADER - SHIM DOOR

35

1

HEADER - SHIM DOOR

36

1

HEADER - SHIM DOOR

37

1

HEADER - SHIM DOOR

38

1

HEADER - SHIM DOOR

39

1

HEADER - SHIM DOOR

40

1

HEADER - SHIM DOOR

41

1

HEADER - SHIM DOOR

42

6

HEADER - SHIM DOOR

43

1

HEADER - SHIM DOOR

44

1

HEADER - SHIM DOOR

45

1

HEADER - SHIM DOOR

46

1

HEADER - SHIM DOOR

47

1

HEADER - SHIM DOOR

48

1

HEADER - SHIM DOOR

49

2

HEADER - SHIM DOOR

50

2

HEADER - SHIM DOOR

51

1

HEADER - SHIM DOOR

52

1

HEADER - SHIM DOOR

53

1

HEADER - SHIM GENERIC 2021.03.04#8

54

2

HEADER - WINDOW

55

6

HEADER - WINDOW

56

2

HEADER - WINDOW

57

2

HEADER - WINDOW

58

2

HEADER - WINDOW

59

2

HEADER - WINDOW

60

2

HEADER - WINDOW

61

2

HEADER - WINDOW

62

2

HEADER - WINDOW

63

2

HEADER - WINDOW

64

4

HEADER - WINDOW

65

2

HEADER - WINDOW

66

2

HEADER - WINDOW

67

6

HEADER - WINDOW

68

4

HEADER - WINDOW

69

2

HEADER - WINDOW

70

2

HEADER - WINDOW

71

2

HEADER - WINDOW

72

2

HEADER - WINDOW

73

2

HEADER - WINDOW

74

2

HEADER - WINDOW

75

2

HEADER - WINDOW

76

2

HEADER - WINDOW

77

6

HEADER - WINDOW

78

4

HEADER - WINDOW

79

6

HEADER - WINDOW

80

2

HEADER - WINDOW

81

2

HEADER - WINDOW

82

2

HEADER - WINDOW

83

2

HEADER - WINDOW

84

4

HEADER - WINDOW

85

4

HEADER - WINDOW

86

2

HEADER - WINDOW

87

2

HEADER - WINDOW

88

2

HEADER - WINDOW

89

2

HEADER - WINDOW

90

2

HEADER - WINDOW

91

3

HEADER - WINDOW

92

2

HEADER - WINDOW

93

4

HEADER - WINDOW

94

2

HEADER - WINDOW

95

2

HEADER - WINDOW

96

2

HEADER - WINDOW

97

2

HEADER - WINDOW

98

4

HEADER - WINDOW

99

2

HEADER - WINDOW

100

16

HYPOTENUSE

101

4

HYPOTENUSE

102

4

HYPOTENUSE

103

4

HYPOTENUSE

104

16

OPPOSITE

105

4

OPPOSITE

106

4

OPPOSITE

107

4

OPPOSITE

108

1

PLATE - 2ND HEADER PLATE

109

3

PLATE - 2ND HEADER PLATE

110

1

PLATE - 2ND HEADER PLATE

111

1

PLATE - 2ND HEADER PLATE

112

1

PLATE - 2ND HEADER PLATE

113

1

PLATE - 2ND HEADER PLATE

114

1

PLATE - 2ND HEADER PLATE

115

2

PLATE - 2ND HEADER PLATE

116

1

PLATE - 2ND HEADER PLATE

117

2

PLATE - 2ND HEADER PLATE

118

1

PLATE - 2ND HEADER PLATE

119

1

PLATE - 2ND HEADER PLATE

120

3

PLATE - 2ND HEADER PLATE

121

2

PLATE - 2ND HEADER PLATE

122

1

PLATE - 2ND HEADER PLATE

123

1

PLATE - 2ND HEADER PLATE

124

1

PLATE - 2ND HEADER PLATE

125

1

PLATE - 2ND HEADER PLATE

126

1

PLATE - 2ND HEADER PLATE

127

1

PLATE - 2ND HEADER PLATE

128

1

PLATE - 2ND HEADER PLATE

129

1

PLATE - 2ND HEADER PLATE

130

3

PLATE - 2ND HEADER PLATE

131

2

PLATE - 2ND HEADER PLATE

132

3

PLATE - 2ND HEADER PLATE

133

1

PLATE - 2ND HEADER PLATE

134

2

PLATE - 2ND HEADER PLATE

135

1

PLATE - 2ND HEADER PLATE

136

2

PLATE - 2ND HEADER PLATE

137

2

PLATE - 2ND HEADER PLATE

138

1

PLATE - 2ND HEADER PLATE

139

1

PLATE - 2ND HEADER PLATE

140

1

PLATE - 2ND HEADER PLATE

141

1

PLATE - 2ND HEADER PLATE

142

1

PLATE - 2ND HEADER PLATE

143

1

PLATE - 2ND HEADER PLATE

144

1

PLATE - 2ND HEADER PLATE

145

2

PLATE - 2ND HEADER PLATE

146

1

PLATE - 2ND HEADER PLATE

147

1

PLATE - 2ND HEADER PLATE

148

1

PLATE - 2ND HEADER PLATE

149

3

PLATE - 2ND HEADER PLATE

150

1

PLATE - 2ND HEADER PLATE

151

1

PLATE - BLOCKING GENERIC 2017.04.11#1

152

2

PLATE - BLOCKING GENERIC 2017.04.11#1

153

2

PLATE - BLOCKING GENERIC 2017.04.11#10

154

3

PLATE - BLOCKING GENERIC 2017.04.11#12

155

3

PLATE - BLOCKING GENERIC 2017.04.11#16

156

2

PLATE - BLOCKING GENERIC 2017.04.11#16

157

2

PLATE - BLOCKING GENERIC 2017.04.11#16

158

2

PLATE - BLOCKING GENERIC 2017.04.11#18

159

1

PLATE - BLOCKING GENERIC 2017.04.11#18

160

1

PLATE - BLOCKING GENERIC 2017.04.11#19

161

1

PLATE - BLOCKING GENERIC 2017.04.11#19

162

1

PLATE - BLOCKING GENERIC 2017.04.11#19

163

5

PLATE - BLOCKING GENERIC 2017.04.11#2

164

2

PLATE - BLOCKING GENERIC 2017.04.11#20

165

1

PLATE - BLOCKING GENERIC 2017.04.11#20

166

1

PLATE - BLOCKING GENERIC 2017.04.11#21

167

1

PLATE - BLOCKING GENERIC 2017.04.11#21

168

1

PLATE - BLOCKING GENERIC 2017.04.11#21

169

1

PLATE - BLOCKING GENERIC 2017.04.11#22

170

1

PLATE - BLOCKING GENERIC 2017.04.11#23

171

1

PLATE - BLOCKING GENERIC 2017.04.11#23

172

2

PLATE - BLOCKING GENERIC 2017.04.11#23

173

1

PLATE - BLOCKING GENERIC 2017.04.11#23

174

1

PLATE - BLOCKING GENERIC 2017.04.11#23

175

1

PLATE - BLOCKING GENERIC 2017.04.11#24

176

2

PLATE - BLOCKING GENERIC 2017.04.11#24

177

1

PLATE - BLOCKING GENERIC 2017.04.11#26

178

1

PLATE - BLOCKING GENERIC 2017.04.11#26

179

1

PLATE - BLOCKING GENERIC 2017.04.11#26

180

1

PLATE - BLOCKING GENERIC 2017.04.11#28

181

1

PLATE - BLOCKING GENERIC 2017.04.11#28

182

20

PLATE - BLOCKING GENERIC 2017.04.11#3

183

1

PLATE - BLOCKING GENERIC 2017.04.11#4

184

1

PLATE - BLOCKING GENERIC 2017.04.11#7

185

1

PLATE - BLOCKING GENERIC 2021.03.04#1

186

1

PLATE - BLOCKING GENERIC 2021.03.04#1

187

1

PLATE - BP-1ST

188

1

PLATE - BP-1ST

189

1

PLATE - BP-1ST

190

1

PLATE - BP-1ST

191

1

PLATE - BP-1ST

192

1

PLATE - BP-1ST

193

1

PLATE - BP-1ST

194

1

PLATE - BP-1ST

195

1

PLATE - BP-1ST

196

1

PLATE - BP-1ST

197

1

PLATE - BP-1ST

198

1

PLATE - BP-1ST

199

1

PLATE - BP-1ST

200

1

PLATE - BP-1ST

201

1

PLATE - BP-1ST

202

1

PLATE - BP-2ND#9

203

1

PLATE - BP-2ND#9

204

1

PLATE - BP-2ND#9

205

1

PLATE - BP-2ND#9

206

1

PLATE - BP-2ND#9

207

87

PLATE - CRIPPLE ABOVE

208

10

PLATE - CRIPPLE ABOVE#1

209

2

PLATE - CRIPPLE ABOVE#1

210

40

PLATE - CRIPPLE ABOVE#1

211

2

PLATE - CRIPPLE ABOVE#1

212

14

PLATE - CRIPPLE ABOVE#1

213

5

PLATE - CRIPPLE ABOVE#2

214

1

PLATE - CRIPPLE ABOVE#2

215

20

PLATE - CRIPPLE ABOVE#2

216

1

PLATE - CRIPPLE ABOVE#2

217

7

PLATE - CRIPPLE ABOVE#2

218

1

PLATE - CRIPPLE GENERIC 2021.03.04#1

219

2

PLATE - CRIPPLE GENERIC 2021.03.04#1

220

3

PLATE - CRIPPLE GENERIC 2021.03.04#1

221

3

PLATE - CRIPPLE GENERIC 2021.03.04#10

222

1

PLATE - CRIPPLE GENERIC 2021.03.04#10

223

2

PLATE - CRIPPLE GENERIC 2021.03.04#10

224

1

PLATE - CRIPPLE GENERIC 2021.03.04#21

225

2

PLATE - CRIPPLE GENERIC 2021.03.04#21

226

3

PLATE - CRIPPLE GENERIC 2021.03.04#21

227

2

PLATE - CRIPPLE GENERIC 2021.03.04#36

228

4

PLATE - CRIPPLE GENERIC 2021.03.04#36

229

1

PLATE - CRIPPLE GENERIC 2021.03.04#61

230

2

PLATE - CRIPPLE GENERIC 2021.03.04#61

231

3

PLATE - CRIPPLE GENERIC 2021.03.04#61

232

1

PLATE - CRIPPLE GENERIC 2021.03.04#62

233

2

PLATE - CRIPPLE GENERIC 2021.03.04#62

234

3

PLATE - CRIPPLE GENERIC 2021.03.04#62

235

1