エクセルＶＢＡでの複数条件でオートフィルタをする方法

質問

VBA初心者です。
例えば、シートに

A 　B
1 桃　　100
2　柿 　150
3 葡萄　400

というデータが書いてあって、ある時は「桃or柿」、またある時は「柿」だけ、「柿or葡萄」というようにＶＢＡでオートフィルタをかけてデータを抽出したいと思っています。

その条件は、別にリストボックスが用意されていて、ユーザが選択した項目に該当するデータだけを取得したいのですが・・・

不特定数の複数項目をオートフィルタの「or」条件で結ぶ事が出来ずに困っています。それともオートフィルタでは無理なのでしょうか？
（知識が乏しくてすみません）

どなたか良い解決策、または別策がありましたらご教授下さい。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

リストボックスということですと、TSV の読み込みの部分は出来上がっているということでしょうか？質問を見た感じでは、AutoFilter よりも、AdvancedFilter のほうが印象的には相応しいように思いますが。

コントロールツールのListBoxを設定してください。

プロパティは、MultiSelect 1-fmMultiSelectMulti　してください。
選択する内容は、ワークシートに書いたものを、ListFillRange に設定してください。
例： K1:K3

ListBox のクリックイベントや、Change イベントですと、そのまま反応してしまいますので、ダブルクリックイベントにしました。ただし、右クリックすると、選択は、すべてクリアされます。

ListBox のアイテム数（30個以内）なら、複数を選択できます。

A1から、リストが存在し、1行目がタイトル行があるという条件の元で成立します。そうでない場合は、また、ご指摘ください。

'シートモジュールを使います。
'--------------------------------------------------------------

Dim Form2 As String
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'リストボックスのダブルクリックで、フィルターが掛けられます。

　　'適当な場所に、Criteria を置いてください。
　　Dim myCriteria As Range
　　Set myCriteria = Range("L1:L2") '空いているところならどこでも可
　　Call MakeForm
　　If Form2 <> "" Then
　　　　myCriteria.Cells(2, 1).FormulaLocal = Form2
　　　　With Range("A1").CurrentRegion
　　　　　　.AdvancedFilter _
　　　　　　Action:=xlFilterInPlace, _
　　　　　　CriteriaRange:=myCriteria, _
　　　　　　Unique:=False
　　　　End With
　　End If
　　Set myCriteria = Nothing
End Sub
Private Sub MakeForm()
'数式を作るサプルーチン
Dim i As Integer
Dim Ar As String
Dim buf As String
Dim Form1 As String
　For i = 0 To ListBox1.ListCount - 1
　　　If ListBox1.Selected(i) Then
　　　　 buf = ListBox1.List(i)
　　　　 Form1 = Form1 & "," & "A2=""" & buf & """"
　　　End If
　　　buf = ""
　Next i
　If Len(Form1) > 1 Then
　　Form1 = Mid(Form1, 2)
　　Form2 = "=OR(" & Form1 & ")"
　End If
End Sub
Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
　　'右クリックしたら、選択をすべて消す
　　If Button = 2 Then
　　　　Dim i As Long
　　　　For i = 0 To ListBox1.ListCount - 1
　　　　　　ListBox1.Selected(i) = False
　　　　Next i
　　End If
　　'フィルターモードクリア
　　If ActiveSheet.FilterMode Then
　　　　ActiveSheet.ShowAllData
　　End If
End Sub

これは、今ひとつ気がかりなのは、TSV でインポートしたときに、空白値が混じりこむことです。その処理をきちんとされていることが条件です。それが不可能というか、部分的に一致する文字を検出する場合は、プログラムの一部を変更するか、空白値を削除するプログラムを書かなくてはなりません。

お礼

こちらのコードが大変参考になりましたので、良回答とさせていただきました。
ありがとうございました！

補足

Wendy02さん、ありがとうございます。
ちょっと上記のコードを取り入れて検証してみました。
ですが、私の知識不足では、理解するのにもう一息ですので、少し質問させてください。

動きを追ってみたら、AdvancedFilter（私は始めて知りました）は、凄いですね。

おっしゃる通り、TSVの取り込みはできています。
Sheet1にそのリストボックスがあり、Sheet2には取り込んだままのTSV（データと余計なコメントなどが入ったもの）があり、Sheet3には、取り込んだTSV内のデータの部分のみ（タイトル行は除去されていました。すみません。）で、データはA1から始まる、という構成になっています。

色々調べたのですが、

　　　　myCriteria.Cells(2, 1).FormulaLocal = Form2
　　　　With Range("A1").CurrentRegion
　　　　　　.AdvancedFilter _
　　　　　　Action:=xlFilterInPlace, _
　　　　　　CriteriaRange:=myCriteria, _
　　　　　　Unique:=False
　　　　End With

の部分が何をしているのか調べ切れませんでした。申し訳ないのですが、少々解説していただけたら、と思います。

回答No.4

Wendy02

こんにちは。
#3の回答者です。

説明するのは、ちょっとややこしいと思います。

>myCriteria.Cells(2, 1).FormulaLocal = Form2

ポイントは、ここの部分のみです。
Form2 は、他のサブルーチンで作った、モジュールレベルの変数、Form2 を呼んでいるのです。そこには、数式が入ります。これを、通常のフィルターオプション形式で書いていったら、２個・３個ならともかく、それ以上は、面倒で溜まったものではありませんので、数式にしました。

この方法は、Microsoft のサポートにハウツーが出ていたと思います。ここのCriteria は特殊で、Criteria というのは、２行あって、その１行目が空欄でなくてはなりません。その２行目に、数式を入れるのです。True Or False の数式が入るのです。それで、フィルタリングするのです。

後は、一般のフィルタオプション（AdvancedFilter)の方法だけです。
一度、ためしに、記録マクロをとってみてもよいと思います。こういうものは、記録マクロも手書きでも、変わらないのです。少し、工夫を加えてやるだけでよいです。（私が、あまり考えていないのがバレますが（^^;）

Range("A1").CurrentRegion
こういうことはお分かりですよね。

一応、連続のセルが続いていたりすると、余計なところまで巻き込んでしまいますので、列数を決める必要があれば、Columns 辺りで、変えなくてはなりません。また、間が空いていたら、End プロパティでとってあげなくてはなりません。

お礼

初めて利用するので、よくわかっていませんでしたが、補足と、お礼の二つは別物なのですね。

補足と重複していまいますが、本当にありがとうございました！

補足

Wendy02さん、ご丁寧にありがとうございました。
解決できそうです。これから盛り込んでいくところです。

実は記録マクロを全然利用しないで手書きをしていたので、AdvancedFilterに気づけなかったのも一因だと、今回の質問を通して痛感いたしました。

それを利用して初めて
データ→フィルタ→フィルタオプションの設定は、AdvancedFilterが用いられている、ということを知りました。
（この「フィルタオプションの設定」もあまり使用しないので、見落としていました）

色々勉強になりました！

回答No.2

hana-hana3

１．オートフィルタで設定可能な条件は２つなので、
　　　「桃or柿」「柿」「柿or葡萄」という設定なら可能です。

Selection.AutoFilter Field:=1, Criteria1:= "=" & "柿", Operator:=xlOr, _
Criteria2:= "=" & "桃"

２．「その条件は、別にリストボックスが用意されていて」というのは、かなり難しくなります。
普通にオートフィルタを設定して、オプションで選択する方が簡単かもしれません。

お礼

オートフィルタ機能が難しい、という判断を早めに出来たので、時間をロスせずに助かりました。
実際に複数「xlOr」を繋げてみたら、動きませんでした。
その検証もせずに、複数をオートフィルタで、と考えていた自分に未熟さを感じます。

ありがとうございました。

補足

hana-hana3さん。
恥ずかしながら「オートフィルタに設定可能な条件は２つ」ということ知りませんでした。

１．逆引きVBAの本を片手に作業しているのですが、「xlOr」を繋げれば、いくつでも指定できるのかと思っていました。

２．例えば、「ANo.1」で補足したデータで説明すると、「果物名リストボックス」には、データの「果物名」に入っている全てのアイテム（重複無し）が表示されています。そこから、ユーザは複数選択を行い、その結果を返したいのです。

色々ありがとうございました。
今回やろうとしていることは、オートフィルタ機能では難しいのかもしれない、ということがわかれば、別の案を検討できます。

回答No.1

pbforce

項目が2つでよければ、手動でオートフィルターがかけれます。

その動作をマクロの記録を使えば、方法は判明しますよ。

お礼

マクロの記録を使用したことが無かったのですが、今回の質問を通して、とても勉強になりました。
貴重なお時間、ありがとうございました。

補足

pbforceさん、早々とありがとうございます。
実際の項目は２つではなく、とりこんだTSVによって変化します。

もう少し説明を付加しますと
　　A　　 　B
1 果物名　値段 (←先ほどこれがぬけていましたが、項目名です)
2 桃　 100
3　 柿 　 150
4 葡萄　 400

とデータがあるとして、別のリストボックスで桃だけ選択されたなら
Range("A1").AutoFilter 1, "桃"

桃と柿が選択されたなら
Range("A1").AutoFilter 1, "桃", xlOr, "柿"

桃と柿と葡萄が選択されたなら
Range("A1").AutoFilter 1, "桃", xlOr, "柿", xlOr, "葡萄"

という風にVBAで書きたいのです。
今回ユーザは、リストボックスのみを操作し、該当するデータを抽出したいのです。説明が足りなくて申し訳ございませんでした。