VBA初心者です。
例えば、シートに
A B
1 桃 100
2 柿 150
3 葡萄 400
というデータが書いてあって、ある時は「桃or柿」、またある時は「柿」だけ、「柿or葡萄」というようにVBAでオートフィルタをかけてデータを抽出したいと思っています。
その条件は、別にリストボックスが用意されていて、ユーザが選択した項目に該当するデータだけを取得したいのですが・・・
不特定数の複数項目をオートフィルタの「or」条件で結ぶ事が出来ずに困っています。それともオートフィルタでは無理なのでしょうか?
(知識が乏しくてすみません)
どなたか良い解決策、または別策がありましたらご教授下さい。
No.3ベストアンサー
- 回答日時:
こんにちは。
リストボックスということですと、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
- 回答日時:
こんにちは。
#3の回答者です。
説明するのは、ちょっとややこしいと思います。
>myCriteria.Cells(2, 1).FormulaLocal = Form2
ポイントは、ここの部分のみです。
Form2 は、他のサブルーチンで作った、モジュールレベルの変数、Form2 を呼んでいるのです。そこには、数式が入ります。これを、通常のフィルターオプション形式で書いていったら、2個・3個ならともかく、それ以上は、面倒で溜まったものではありませんので、数式にしました。
この方法は、Microsoft のサポートにハウツーが出ていたと思います。ここのCriteria は特殊で、Criteria というのは、2行あって、その1行目が空欄でなくてはなりません。その2行目に、数式を入れるのです。True Or False の数式が入るのです。それで、フィルタリングするのです。
後は、一般のフィルタオプション(AdvancedFilter)の方法だけです。
一度、ためしに、記録マクロをとってみてもよいと思います。こういうものは、記録マクロも手書きでも、変わらないのです。少し、工夫を加えてやるだけでよいです。(私が、あまり考えていないのがバレますが(^^;)
Range("A1").CurrentRegion
こういうことはお分かりですよね。
一応、連続のセルが続いていたりすると、余計なところまで巻き込んでしまいますので、列数を決める必要があれば、Columns 辺りで、変えなくてはなりません。また、間が空いていたら、End プロパティでとってあげなくてはなりません。
この回答への補足
Wendy02さん、ご丁寧にありがとうございました。
解決できそうです。これから盛り込んでいくところです。
実は記録マクロを全然利用しないで手書きをしていたので、AdvancedFilterに気づけなかったのも一因だと、今回の質問を通して痛感いたしました。
それを利用して初めて
データ→フィルタ→フィルタオプションの設定は、AdvancedFilterが用いられている、ということを知りました。
(この「フィルタオプションの設定」もあまり使用しないので、見落としていました)
色々勉強になりました!
初めて利用するので、よくわかっていませんでしたが、補足と、お礼の二つは別物なのですね。
補足と重複していまいますが、本当にありがとうございました!
No.2
- 回答日時:
1.オートフィルタで設定可能な条件は2つなので、
「桃or柿」「柿」「柿or葡萄」という設定なら可能です。
Selection.AutoFilter Field:=1, Criteria1:= "=" & "柿", Operator:=xlOr, _
Criteria2:= "=" & "桃"
2.「その条件は、別にリストボックスが用意されていて」というのは、かなり難しくなります。
普通にオートフィルタを設定して、オプションで選択する方が簡単かもしれません。
この回答への補足
hana-hana3さん。
恥ずかしながら「オートフィルタに設定可能な条件は2つ」ということ知りませんでした。
1.逆引きVBAの本を片手に作業しているのですが、「xlOr」を繋げれば、いくつでも指定できるのかと思っていました。
2.例えば、「ANo.1」で補足したデータで説明すると、「果物名リストボックス」には、データの「果物名」に入っている全てのアイテム(重複無し)が表示されています。そこから、ユーザは複数選択を行い、その結果を返したいのです。
色々ありがとうございました。
今回やろうとしていることは、オートフィルタ機能では難しいのかもしれない、ということがわかれば、別の案を検討できます。
オートフィルタ機能が難しい、という判断を早めに出来たので、時間をロスせずに助かりました。
実際に複数「xlOr」を繋げてみたら、動きませんでした。
その検証もせずに、複数をオートフィルタで、と考えていた自分に未熟さを感じます。
ありがとうございました。
No.1
- 回答日時:
項目が2つでよければ、手動でオートフィルターがかけれます。
その動作をマクロの記録を使えば、方法は判明しますよ。
この回答への補足
pbforceさん、早々とありがとうございます。
実際の項目は2つではなく、とりこんだ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で書きたいのです。
今回ユーザは、リストボックスのみを操作し、該当するデータを抽出したいのです。説明が足りなくて申し訳ございませんでした。
マクロの記録を使用したことが無かったのですが、今回の質問を通して、とても勉強になりました。
貴重なお時間、ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Visual Basic(VBA) エクセルVBAコピー 2 2022/06/08 21:45
- Excel(エクセル) Excel Powerクエリーの質問、行数指定は可能でしょうか? 2 2022/08/22 12:54
- Visual Basic(VBA) VBA初心者です。 VBAで行単位で条件付き書式の色をカウントしたいです。 大量のデータがあるExc 3 2022/06/08 10:00
- Visual Basic(VBA) VBA初心者です。 VBAで行単位で条件付き書式の色をカウントしたいです。 大量のデータがあるExc 3 2022/06/08 10:02
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) 【VBA】データを入力後に,同一シート内に履歴として転記するVBAコードを教えていただきたいです。 3 2022/11/16 01:37
- Visual Basic(VBA) 複数シートの複数列に入力されているデータを重複なしで抽出するVBAを作りたいです。 9 2022/06/17 10:33
- Excel(エクセル) エクセルのフィルターを複数シートに連動させたいです。 エクセルファイルに15シートあります。 そのう 2 2022/05/01 21:47
- Visual Basic(VBA) 動かなくなってしまった古いVBAを動くようにしたい 8 2022/09/20 13:57
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
乾燥剤(生石灰)を水に濡らし...
-
生石灰の乾燥剤を濡らしてしま...
-
UFOのかやくの乾燥キャベツって...
-
柿落としの意味について教えて...
-
男性を「渋い」というのは褒め...
-
スーパーでキャベツ買ったら中...
-
黒檀(こくたん)て何の木ですか?
-
切り株をスライス(厚み2センチ...
-
家庭菜園泥棒の対策
-
美味しんぼで
-
【どちらにしようかな 天の神様...
-
__人前での作文の読みかた___
-
柿の種の保存方法は?
-
干し柿のカビについて
-
乾燥した板が水を吸った後変形...
-
甘くない柿を甘くしたいです
-
柿を柔らかくしたい
-
食用でない干し柿を食べてしまった
-
今日、プラ板を作ろうとしてレ...
-
硬くて甘い柿が食べたいのに・...
おすすめ情報