【VBA】ドロップダウンリストに絞り込み機能を追加する方法を紹介

VBAでドロップダウンリストを設定したい！

こんな要望にお応えします。

この記事では、実際にVbaを操作している画像を用いて、絞り込み機能付きのドロップダウンリストの設定方法について解説しています。

Vba未経験者にもわかりやすい内容になっています。

記事の最後では、

絞り込み機能付きドロップダウンリストが設定したエクセルファイルをダウンロード

することが可能です。

コードをコピペして職場で お使いの エクセル ファイルにマクロを導入したい！

ドロップダウンリストのマクロを設定するときのサンプルコードとして保存しておきたい！

こんな場面で役立てることができます。

VBAを必要としない「データの入力規則」を利用したドロップダウンリストの導入だけでもエクセル業務の効率をアップさせることができますが、

この記事で紹介する絞り込み機能付きドロップダウンリストは効率化のレベルが桁違いです。

日頃お使いのエクセルファイルにドロップダウンリストが多数設定されているなら、絞り込み機能付きドロップダウンリストへの変更がおすすめ。

エクセル業務の効率をアップさせたい！と考えている方は是非参考にしてみてください。

ドロップダウンリストの絞り込み機能とは

ドロップダウンリストの絞り込み機能とは、

条件によってドロップダウンリストのリスト内容が自動的に変化する

というものです。

例えば、

ドロップダウンリストに20個分のデータが表示されていても、本当に表示させたい項目を5個に絞り込んで表示させる

このような処理が可能です。

言葉での説明だけではわかりにくいと思うので、サンプル事例を使って絞り込み機能がどういうものか具体的にご紹介していきます。

サンプル事例 条件によってリスト項目を変化させるマクロ

サンプル事例として、

1列目(ジャンル)に入力されたテキスト内容によって、2列目(種別)に表示されるリスト項目が自動的に変化させる

というマクロをご紹介します。

この絞り込み機能を使えば、

不要な項目が表示されないのでリストの選択操作がめちゃくちゃ楽になります。

長年使い続けているエクセルで発生しがちな、

リスト化される項目が多くなりすぎて選択しづらくなる

という問題を解決することができます。

条件によってリスト項目を変化させるマクロのコード

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
'1列目の最終行数を取得し変数rmに格納する
rm = Cells(Rows.Count, 1).End(xlUp).Row
'A列とB列のドロップダウンリストを削除
Range("A:A").Validation.Delete
Range("B:B").Validation.Delete
'変数Kに「果物」を格納する
If Target.Column = 1 And Target.Row <> 1 Then
'「果物」「野菜」をリスト化する
With Target.Validation
.Add Type:=xlValidateList, Formula1:="果物,野菜"
End With
Else
End If
'2行目から変数rmまでを繰り返す
For i = 2 To rm
On Error Resume Next
Dim VA As String
'クリックしたセルの1つ左のセルの情報を変数VAに格納する
VA = Cells(Target.Row, Target.Column - 1)
'変数Kに「果物」を格納する
K = "果物"
'変数Tに「野菜」を格納する
T = "野菜"
'もし2列目をクリックし、かつ変数VAが変数Kだったら
If (Target.Column = 2) And (VA = K) Then
'ドロップダウンリストに「リンゴ」「みかん」を表示する
With Target.Validation
.Add Type:=xlValidateList, Formula1:="リンゴ,みかん"
.ShowError = False
End With
'もし2列目をクリックし、かつ変数VAが変数Tだったら
ElseIf (Target.Column = 2) And (VA = T) Then
With Target.Validation
'ドロップダウンリストに「白菜」「玉ねぎ」を表示する
.Add Type:=xlValidateList, Formula1:="白菜,玉ねぎ"
.ShowError = False
End With
End If
Next
If Err <> 0 Then
Err.Clear
End If
End Sub

サンプルコードの内容は、

A列に　「果物」と入力されていればb列には「りんご」「みかん」と表示。

A列に　「野菜」と入力されていればb列には「白菜」「玉ねぎ」と表示。

です。

A列の「ジャンル」を「会社名」、B列の「種別」を「担当者名」に変更するだけで、仕事に応用できると感じてもらえるのではないでしょうか。

コードには解説を細かく記載されているので、業務内容に合ったマクロにアレンジするとき参考にしてください。

ドロップダウンリストを表示させる基本的なマクロ3選

ドロップダウンリストを設定する3種類の基本的なマクロを解説していきます。

1. コードにリスト項目を直接入力する方法

2. 一覧表の情報(縦方向)をリスト化する方法

3. 一覧表の情報(横方向)をリスト化する方法

上記3つの方法を理解すれば、

絞り込み機能付きのドロップダウンリストの作成は簡単

と感じるようになるでしょう。

1.コードにリスト項目を直接入力するマクロ

VBAで最も簡単にドロップダウンリストを設定する方法です。

簡単なだけに、

・条件によってリスト項目を変化させることができない
・リスト項目を変更するにはコードの編集が必要

といったデメリットがあります。

「VBAでドロップダウンリストを表示さる基本コードの確認」程度の気持ちで参考にしてください。

サンプルコード

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
'A5セルのドロップダウンリストを削除
Range("A5").Validation.Delete
'もし5行目かつ1列目(A5セル)をクリックしたとき
If (Target.Row = 5 And Target.Column = 1) Then
'ドロップダウンリストを下記の条件で表示させる
With Target.Validation
.Add Type:=xlValidateList, Formula1:="正解,不正解,三角"
End With
Else
End If
End Sub

実行結果

コード内の「Formula1:="正解,不正解,三角"」を編集するだけでリスト項目を変更することができます。

2.一覧表の情報(縦方向)をリスト化するマクロ

一覧表の情報をリスト化する仕組みは、仕事で最も利用されるドロップダウンリストです。

・一覧表を編集するだけでリスト項目を変更できる
・誰でも感覚的にドロップダウンリストを利用することができる

といったメリットがあります。

一覧表の情報をリスト化するマクロは汎用性が高いので、いろんなエクセルファイルに導入することができるはずです。

是非使い方をマスターしてください。

サンプルコード

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
'５列目の最終行数を取得し変数rmに格納する
rm = Cells(Rows.Count, 5).End(xlUp).Row
'変数ristoを宣言
Dim risuto As Variant
'Transpose関数を使って変数risutoに配列化した一覧表のテキストを格納
risuto = WorksheetFunction.Transpose(Range(Cells(3, 5), Cells(rm, 5)))
'A5セルのドロップダウンリストを削除
Range("A5").Validation.Delete
'もし5行目かつ1列目(A5セル)をクリックしたとき
If (Target.Row = 5 And Target.Column = 1) Then
'ドロップダウンリストを下記の条件で表示させる
With Target.Validation
.Add Type:=xlValidateList, Formula1:=Join(risuto, ",")
End With
Else
End If
End Sub

実行結果

E列のテキストを編集するとドロップダウンリストの項目が自動的に変化します。

また、E列の一覧表の項目を追加したり削除してもドロップダウンリストの項目が自動変化します。

一覧表の項目が増えたからドロップダウンリストに表示させる範囲設定を修正しなければならない！という手間が一切必要ありません。

3.一覧表の情報(横方向)をリスト化するマクロ

一覧表の情報をリスト化する場合、上記で紹介した「一覧表の縦方向の項目をリスト化」がほとんどだと思います。

極稀に「一覧表の横方向の項目をリスト化したい！」ことが発生するはずです。

私の場合、エクセル家計簿に「絞り機能付きドロップダウンリスト」を設定する場面で横方向の項目をリスト化するマクロが必要になりました。

エクセル家計簿に設定したドロップダウンリストはこんな感じ

絞り込み機能付きのドロップダウンリストが多数設定されているのでめちゃくちゃ使いやすい！！

いろんな便利マクロが設定されたエクセル家計簿は下記の記事で詳しく紹介しています。

気になる方は是非チェックしてみてください。

一覧表の横方向の情報をリスト化するマクロのサンプルコードがこちら

サンプルコード

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
'2行目の最終列数を取得し変数retuに格納する
retu = Cells(2, Columns.Count).End(xlToLeft).Column
'変数ristoを宣言
Dim risuto As Variant
'Transpose関数2回繰り返し、変数risutoに配列化した一覧表のテキストを格納
risuto = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range(Cells(2, 2), Cells(2, retu))))
'A5セルのドロップダウンリストを削除
Range("A5").Validation.Delete
'もし5行目かつ1列目(A5セル)をクリックしたとき
If (Target.Row = 5 And Target.Column = 1) Then
'ドロップダウンリストを下記の条件で表示させる
With Target.Validation
.Add Type:=xlValidateList, Formula1:=Join(risuto, ",")
End With
Else
End If
End Sub

実行結果

一覧表の縦方向の情報を取得するのか、横方向の情報を取得するのか、

はTranspose関数の使い方で確定させることができます。

横方向の情報を取得する場合、Transpose関数を2回繰り返し使うことになるのでわかりにくいと思いますが、

最初のうちは「こーゆーもの」として覚えてしまって問題ありません。

繰り返し使っていくうちに使いこなせるようになっていくはずです。

条件付きドロップダウンリストの設定代行サービス

下記のサイトでは、

お使いのエクセルに絞り込み機能付きのドロップリストの設定代行

を承っております。

• 一覧表のこの場所にリストを表示させたい

• 別シートにあるこんな一覧表を表示させたい

• こんな条件でリスト内容を表示させたい

依頼方法は上記のようなザックリした内容でオッケーです。

どのようなドロップダウンリストを表示させたいのか聞き取りを行い、最善の処理をするマクロをご提案させて頂きます。

>>>マクロ開発依頼はこちら

絞り込み機能付きのドロップダウンリストを表示するマクロについての情報は、

ネットや参考書でもあまり紹介されていません。

ドロップダウンリストを導入してエクセル業務の効率をアップさせたい！という方は、マメBlogのマクロ開発依頼サービスからお気軽にお問い合わせください。

絞り込み機能付きドロップダウンリストマクロが設定されたエクセルファイルのダウンロード

サンプル事例として紹介している「４種類の絞り込み機能付きドロップダウンリストマクロ」が設定されたエクセルファイルをダウンロードしてお使い頂くことができます。

基本的なドロップダウンリストマクロから条件によってリスト項目が変化するドロップダウンリストマクロが設定されているので、

仕事でお使いのエクセルファイルにドロップダウンリストを設定したい！という方におすすめです。

新たにドロップダウンリストを設定する際、このエクセルファイルをコピペ用として利用しています。