VBAオートフィルターについて

VBA初心者です。
オートフィルターで、〇〇という語句を含み、××or▲▲or◇◇を含まないデータを抽出したいです。
↓自分で書いたプログラムです。

Dim arr(2) As String
arr(0) = "××"
arr(1) = "▲▲"
arr(2) = "◇◇"

Range("B1").AutoFilter Field:=2, Criteria1:="*"〇〇 "*", Operator:=xland, Criteria2:="<>*arr*"


〇〇を含み　かつ　××or▲▲or◇◇を含まないデータなので、and条件かと思いましたが、
この言語だとデータが一つも抽出されません。
どのように改善すべきでしょうか？

No.1 回答者： mygoonickname 回答日時： 2021/11/15 17:10

こんばんは。

オートフィルターで、そこまで細かく指定できないのでは？と思います。
マクロの記録で、オートフィルターの設定できる範囲が限界かと思います。

http://officetanaka.net/excel/vba/tips/tips155.htm

No.2 回答者： Qchan1962 回答日時： 2021/11/15 17:33

こんばんは、

タイプが間違っているのでは？

Range("B1").AutoFilter Field:=2, Criteria1:="*〇〇*", Operator:=xlAnd, Criteria2:="<>*arr*"

No.3 回答者： めぐみん_ 回答日時： 2021/11/15 18:01

Criteria2:="<>*arr*"

変数名を文字列扱いしてませんか？

Criteria2:="<>*" & arr & "*"

でも未検証なのでどちらかと言えばAdvancedfilterがよさそうな？

この回答へのお礼

ありがとうございます。
私も書式の問題かと思い、
Criteria2:="<> & arr & "　としましたが、
「型が一致しません」というメッセージが出てデバックしてしまいます、、、

お礼日時：2021/11/16 13:50

No.4 回答者： fujillin 回答日時： 2021/11/15 20:42

こんばんは

条件が複雑そうなので、作業列に条件式を設定して、その列を基準にフィルターを掛けるのが簡単そうに思います。
作業列を設けるのが嫌ならば、直接VBA内で判別して、表示/非表示を設定してしまう方法も考えられます。
また、No3様がご指摘のように、フィルターオプションを利用する方法もあるでしょう。

どうしても、AutoFilterに拘りがあるのなら、先にVBAで判別して、それをfilterの条件にしてしまうとか。
この方法だと、内容的に処理が2重手間のようになってしまいますけれど、ご参考までに。

※ シートの状態がどのようになっているのか不明なので、以下では、B列のみにフィルターを掛けるようにしてあります。

Sub Sample()
Dim r As Range, c()
Dim i As Long, tmp As String
Dim arr
Const include = "〇〇"
arr = Array("××", "▲▲", "◇◇")

Set r = Intersect(Range("B1").CurrentRegion, Columns(2))
ReDim c(1 To r.Rows.Count)
For i = 1 To r.Rows.Count
tmp = Cells(i, 2).Text
c(i) = InStr(tmp, arr(0)) + InStr(tmp, arr(1)) + InStr(tmp, arr(2))
If InStr(tmp, include) And c(i) = 0 Then c(i) = tmp Else c(i) = ""
Next i
r.AutoFilter
r.AutoFilter Field:=1, Criteria1:=c, Operator:=xlFilterValues
End Sub

No.5 回答者： fujillin 回答日時： 2021/11/15 22:11

No4です。

再度見てみたら、
No4のコードだと、B列に空白セルが存在するとその行も抽出されてしまいますね。

失礼いたしました。
以下、修正したコードです。

Sub Sample()
Dim r As Range, c()
Dim i As Long, f As Long, tmp As String
Dim arr
Const include = "〇〇"
arr = Array("××", "▲▲", "◇◇")

Set r = Intersect(Range("B1").CurrentRegion, Columns(2))
ReDim c(1 To r.Rows.Count)
For i = 1 To r.Rows.Count
tmp = Cells(i, 2).Text
f = InStr(tmp, arr(0)) + InStr(tmp, arr(1)) + InStr(tmp, arr(2))
If InStr(tmp, include) And f = 0 Then c(i) = tmp
Next i
r.AutoFilter
r.AutoFilter Field:=1, Criteria1:=c, Operator:=xlFilterValues
End Sub

No.6 回答者： ママチャリ 回答日時： 2021/11/18 09:45

お望みの方法とは違うかもですが、こんなのはどうでしょう。

Sub sample()
Dim Cri As Variant
Cri = WorksheetFunction.Transpose(Range("C:C"))
Cri = Filter(Cri, "〇〇")
Cri = Filter(Cri, "××", False)
Cri = Filter(Cri, "▲▲", False)
Cri = Filter(Cri, "◇◇", False)
Range("B1").AutoFilter Field:=2, Criteria1:=Cri, Operator:=xlFilterValues
End Sub