AutoFilterを行う際、部分一致でフィルターを掛けることは可能でしょうか？

前提・実現したいこと

VBAにて、変数に格納した複数の文字列を用いて、部分一致のフィルター掛けを行うことは可能でしょうか？

単に完全一致の値でフィルターを掛けるだけであれば、検索用文字列が複数の値であっても1次元配列に変換し、変数として保存することで実現が可能でした。

しかし、実運用で用いる元データを確認すると、文字列が部分一致となっている文字列が数多く存在しました。

例えば、「株式会社」〇〇のように、無視すればよい文字列が決まっているのであれば、「貼り付け時に"株式会社"は除く」のような処理を追加するだけだと思うのですが、実際のデータでは多数のパターンが混ざっており、一概に「〇〇を除く」という処理では対応しきれませんでした。

データの例
フィルター用文字列データ
project1
project2
project3

フィルターを掛けたいデータ
project project1
project2
休止中 project3

下記のコードで試してみたのですが、完全一致する文字列のみフィルターがかかり、部分一致の文字列にはフィルターが掛けられませんでした。
この問題を解決し、部分一致のみでもフィルター掛けを行う方法がありましたら、ご教示いただければと思います。

発生している問題・エラーメッセージ

エラーは発生しておりません。

該当のソースコード

VBA
1'
2        'フィルタ結果のE列:重複を除いてDictionaryに格納
3        For f = 2 To LastRow
4            If .Rows(f).Hidden = False Then
5                buf = Cells(f, 5).Value
6                If Not Dic.Exists(buf) Then
7                    Dic.Add buf, buf
8                End If
9            End If
10        Next f
11        
12'Dicに保存した文字列を、作業用シートに保存する。
13Range("E2:E10000").Copy '検索用文字列をコピー
14
15'作業用Excelをアクティブに
16    myBook.Activate
17    ofgrSht.Activate
18    
19    
20'アクティブブック(sheet1)に検索用の値を貼り付け
21Worksheets("sheet1").Range("G2").PasteSpecial _
22                                 Paste:=xlPasteValues, _
23                                 Operation:=xlNone, _
24                                 SkipBlanks:=False, _
25                                 Transpose:=False
26                                 
27'検索用文字列を1次元配列で変数に挿入
28trans = WorksheetFunction.Transpose(Range("G2:G10000"))
29
30'~~~~~別ブックでの作業~~~~~
31'貼り付け先のシートで、さらにフィルターを掛ける。検索値はtransに封入
32Range("A4").AutoFilter 1, trans, xlFilterValues
33
34

試したこと

上記コードにて、変数に挿入した値を用いてフィルターを掛けました。
フィルター自体は正常にかかったのですが、フィルター掛けされたのは完全一致の文字列だけでした。

補足情報（FW/ツールのバージョンなど）

Excel 2016

行動規範の内容に同意します

回答2件

ベストアンサー

AutoFilterで部分一致は、2項目までならできますが、3項目以上ではできないようです。

Excel Util: 【VBA】フィルターでの完全一致・部分一致

AdvancedFilterを使うと、3項目以上でも部分一致で抽出可能です。

EXCEL VBA AdvancedFilterメゾット・フィルターオプション・データ抽出・抽出結果・別シート・重複削除

コードサンプル

vba
1Sub AdvancedFilterTest()
2
3    Range("A8").CurrentRegion.AdvancedFilter _
4            Action:=xlFilterInPlace, _
5            CriteriaRange:=Range("A1").CurrentRegion
6
7End Sub

投稿2021/10/01 15:43

hatena19

総合スコア33795

same_2614869

2021/10/02 14:18

こちらご回答頂きまして誠にありがとうございます。確認が遅れて申し訳ございません。恥ずかしながらAdvancedFilterの存在は存じ上げず、こちらを用いれば自分の要求が達成できると感じています。 1点追加で確認したいのですが、AdvancedFilterでは、変数に保存した値でフィルターを掛けることはできないのでしょうか？(変数名：trans) 下記のコードで実行したのですが、オブジェクトが必要という旨のエラーが発生してしまいます。 (書き方が悪いのかもしれませんが…) すでにご回答頂いている中で大変恐縮ではあるのですが、ご教示いただければと思います。実行時エラー '424' オブジェクトが必要です。 Range("A4").CurrentRegion.AdvancedFilter _ Action:=xlFilterInPlace, _ criteriarange:=trans.CurrentRegion

hatena19

2021/10/02 15:14 編集

AdvancedFilter では条件に配列変数は無理ですね。引数名が CriteriaRange からも分かるように、Range（シート上のセル範囲）でなければいけません。表示したくなければ、非表示のシート上のセル範囲にすればいいでしょう。

same_2614869

2021/10/02 16:48

重ねてのご回答ありがとうございます。変数は利用できない旨承知いたしました。範囲指定ということであれば、変数は使わず別ブックのシート上に存在するセル範囲を指定するのも難しいでしょうか？ (別のブックではなくとも、同一ブックの別のシート上でも) 後出しになってしまうのですが、元にするデータや処理したいデータの行・列が毎回異なるので、同一シート上に文字列を配置したくないという思いがあります。

hatena19

2021/10/03 00:09

別シートでも、別ブックのシートでも可能です。（聞く前に試してみればすぐわかることてす。）それに、上で非表示のシートでもいいとコメントしてますよね。

same_2614869

2021/10/03 16:02

こちらお手を煩わせてしまい申し訳ありませんでした別ブックでも可能ということですので試したのですが、書き方の問題かコンパイルできずエラーとなってしまったので確認させていただきました。可能であるという確証は得られたので、あとはこちらで試させていただきます。ご回答ありがとうございました。

hatena19

2021/10/03 16:21 編集

別ブックは下記のようなコードです。 Range("A8").CurrentRegion.AdvancedFilter _ 　　Action:=xlFilterInPlace, _ 　　CriteriaRange:=Workbooks("another.xlsx").Sheets(1).Range("A1").CurrentRegion もちろん別ブックは開いてないとダメですが。

行動規範の内容に同意します