Categories: VBA

VBA│オートフィルタの設定と解除。空白、文字列、数値の絞り込み

この記事では、Excelのシートにオートフィルタ設定する・解除するといった基本動作のほか、オートフィルタ空白や文字列、数値をキーに絞り込む方法を実際のVBAコードも含めて説明します。

Excelは、表計算ソフトといった特性上、大量のレコードからデータを加工、分析する機会はとても多く、オートフィルタを日常的に設定している方も多いでしょう。

VBAにおいてもオートフィルタを習得することで、業務自動化の適用範囲が一気に広がります。

では、オートフィルタの設定・解除といった基本動作から絞り込みの使い方まで、早速VBAコードを見ていきましょう。

なお、日付に関しては、相当な文字数となりますので別記事で紹介しています。必要に応じてご参考ください。

オートフィルタの設定・解除

この章では、ExcelのA列-E列の範囲に作成されている表に、オートフィルタを設定するVBAコードを紹介します。

なお、オートフィルタを設定する場合と解除する場合のVBAコードは同じです。つまり、同一のVBAコードを実行する都度、オートフィルタ設定・解除が切り替わります。

基本的な設定と解除

Sub Sample()

Range(“A1”).AutoFilter


End Sub

解説

上記のVBAコードを実行するだけで、Excelシートにオートフィルタを設定することができます。

設定と解除は同じVBAコードとなりますので、Excelにすでにオートフィルタが設定されている状態で実行すると、オートフィルタが解除されます。

なお、コード内で Range(“A1”) を指定していることについて、通常、オートフィルタを設定する場合には、対象の表内のセルを指定することが多いと思いますが、実際には、表の外のセルを指定しても、結果は変わりません。

今回の例ですと、Range(“G5“).AutoFilter というVBAコードを実行しても、オートフィルタはセルA1からセルE1の範囲に設定されます。

オートフィルタの絞り込み

この章では、オートフィルタによる様々な絞り込みをVBAで実現するためのコードを紹介します。

具体的には、複数条件の絞り込みや空白、文字列・数値といったデータ形式ごとの設定方法など、どれも実用性の高い内容です。

絞り込みの基本

Sub Sample()

Range(“A1″).AutoFilter Field:=3, Criteria1:=”営業”


End Sub

解説

Range(“A1”).AutoFilter Field:=3, Criteria1:=”営業”

最初のAutoFilterメソッドに関しては、単独で使う場合とは少し動作が変わってきます。予め対象のExcelシートにオートフィルタが設定されていても、オートフィルタは解除されません。オートフィルタが設定されていない状態であれば、オートフィルタを設定するとともに、 Field 以降の絞り込みが実行されます。


Range(“A1”).AutoFilter Field:=3, Criteria1:=”営業”

Field:=は、フィルタを設定するための引数で、対象の列を番号で指定します。今回の例の場合には、C列(3行目)となりますので、引数は「3」となります。


Range(“A1”).AutoFilter Field:=3, Criteria1:=”営業”

Criteria1:=は、フィルタを設定するための引数で、絞り込む文字を指定します。今回の例の場合には、C列が「営業」となっているレコードを絞り込みの対象としていますので、引数は「営業」となります。

複数条件の絞り込み

複数列

前節では、C列が営業になっているレコードを対象に絞り込みをしました。今回は、C列が営業かつD列がMGになっているレコードを対象に、オートフィルタを使って複数条件(複数列)で絞り込む場合のVBAコードを紹介します。

Sub Sample()

With Range(“A1”)

.AutoFilter Field:=3, Criteria1:=”営業”
.AutoFilter Field:=4, Criteria1:=”MG”
End With

End Sub

上記のコードで、C列(3列目)を「営業」という文字列で、D列(4列目)を「MG」という文字列で絞り込むオートフィルタを設定することができます。

and、orを使った複数条件

ここでは、プログラムではおなじみの「and,or」を使って絞り込みの条件指定をするVBAコードを紹介します。


orのVBAコード

C列(3列目)が「営業」または「経理」になっているレコードをオートフィルタを使って絞り込みます。

Sub Sample()

Range(“A1″).AutoFilter Field:=3, Criteria1:=”営業”, _

Operator:=xlOr, Criteria2:=”経理”

End Sub


andのVBAコード

E列(5列目)の通話料金が、「5,000円以上10,000円未満」になっているレコードをオートフィルタを使って絞り込みます。

Sub Sample()

Range(“A1″).AutoFilter Field:=5, Criteria1:=”>=5000″, _

Operator:=xlOr, Criteria2:=”経理”

End Sub

空白で絞り込む

今回は、D列(4列目)が空白になっているレコードをオートフィルタを使って絞り込む場合のVBAコードを紹介します。

Sub Sample()

Range(“A1″).AutoFilter Field:=4, Criteria1:=”=”


End Sub

上記のコードで、D列(4列目)が空白になっているレコードを絞り込むオートフィルタを設定することができます。

空白以外で絞り込む

ここでは、D列(4列目)が空白以外になっているレコードをオートフィルタを使って絞り込む場合のVBAコードを紹介します。

Sub Sample()

Range(“A1″).AutoFilter Field:=4, Criteria1:=”<>”


End Sub

上記のコードで、D列(4列目)が空白以外になっているレコードを絞り込むオートフィルタを設定することができます。

文字列で絞り込む

絞り込みの基本の節を含めて、この章ではすでにオートフィルタによる文字列の絞り込み方法を紹介していますので、ここでは特定の文字列以外を絞り込む場合や、ワイルドカードを含めて絞り込む場合のVBAコードを紹介します。

〇〇以外で絞り込む

C列(3列目)が経理以外になっているレコードをオートフィルタを使って絞り込む場合のVBAコードを紹介します。

Sub Sample()

Range(“A1″).AutoFilter Field:=3, Criteria1:=”<>経理”


End Sub

ワイルドカード(* ?)を使って絞り込む

Criteria1プロパティでは、「*」「?」ワイルドカードを使うことができます。「*」は文字数を指定しない場合、「?」は文字数を指定する場合に使います。

ワイルドカード「*」の使い方
〇で始まる〇*
〇で終わる*〇
〇を含む*〇*

例:都道府県リスト

Sub Sample()

Range(“A1″).AutoFilter Field:=1, Criteria1:=”*川”


End Sub

末尾が「川」の都道府県が絞り込まれます。文字数は関係ありません

ワイルドカード「?」の使い方
〇で始まる3文字〇??
〇で終わる3文字??〇
〇を真ん中に含む3文字?〇?

例:都道府県リスト

Sub Sample()

Range(“A1″).AutoFilter Field:=1, Criteria1:=”?川”


End Sub

文字数が2文字かつ末尾が「川」の都道府県が絞り込まれます。末尾が「川」であっても、文字数が3文字の神奈川は絞り込まれません。

数値で絞り込む

オートフィルタを使って数値で絞り込む場合については、比較演算子の考え方のみの紹介とします。

5,000とイコールCriteria1:=”4000″
5,000より大きいCriteria1:=”>4000″
5,000以上Criteria1:=”>=4000″
5,000より小さいCriteria1:=”<4000″
5,000以下Criteria1:=”<=4000″

例えば、ある表の5列目で「5,000円以下」のレコードを絞り込みたい場合のコードは、下記の通りです。

Sub Sample()

Range(“A1″).AutoFilter Field:=5, Criteria1:=”<=5000”


End Sub

Webs

元事務処理担当者として、プログラミングを活用した業務改善を推進している。プログラマ出身ではない事務職の現場目線で情報を発信。 その他、webs-studio.jpをメインに、複数のブログサイトを立ち上げており、合計で月間10万PVのメディア運営を行っている。 姉妹サイト一覧