Categories: VBA

VBA│オートフィルタで絞り込んだ範囲をコピー・件数を確認する

この記事では、Excelシートに設定されたオートフィルタで絞り込んでいる範囲をコピーする、または絞り込んでいる件数を確認する方法を実際のVBAコードも含めて説明します。

Excelのオートフィルタは、大量のレコードから任意のデータを取り出したり、該当するレコード数を確認するといったデータ活用・分析が必要な様々なシーンで使われます。

必然的に、VBAで同等の処理が必要になる場合も多いでしょう。

早速、オートフィルタで絞り込んだ範囲をコピーする、絞り込んでいる件数を確認するためのVBAコードを見ていきましょう。

なお、オートフィルタの基本的な使い方や日付の絞り込みの方法については、当サイトの別記事で発信していますので、当記事末尾でリンクを紹介します。

オートフィルタで絞り込んだ範囲をコピーする

この章では、オートフィルタを使ってExcelのD列(4列目)が「MG」のキーワードで絞り込まれたデータ範囲をコピーして、「コピー先」という別シートに貼り付けるVBAコードを紹介します。

オートフィルタで絞り込みされているシートでは、左の行番号を確認すれば分かるように、絞り込まれた行以外も存在しています。(例えば、2行目-5行目です)

ここで紹介するVBAコードを実行すれば、絞り込まれている行以外はコピーされません。なお、「コピー先」のシートは実行前は白紙です。

絞り込んだ範囲をコピーするVBAコード

Sub Sample()

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

Range(“A1”).CurrentRegion.Copy Sheets(“コピー先”).Range(“A1”)

End Sub

解説

上記のVBAコードを実行すると、「コピー元」のシートのオートフィルタで絞られているレコードのみ、「コピー先」のシートにコピーされます。

このように、オートフィルタで絞られた範囲のみをコピーする場合には、CurrentRegionプロパティ を使います。

オートフィルタで絞り込んだ件数を確認する

この章では、オートフィルタを使ってExcelのC列(3列目)が「営業」のキーワードで絞り込まれたデータの件数をカウント・取得するVBAコードを紹介します。

なお、C列の先頭行は見出し行となっていますので、VBAコード内で対処する必要があります。

絞り込んだデータ件数をカウント・取得するVBAコード

Sub Sample()

Dim cnt As Long

cnt = WorksheetFunction.Subtotal(3, Range(“C:C”)) – 1
MsgBox cnt & “レコードです”

End Sub

解説

上記のVBAコードを実行すると、オートフィルタでC列(3列目)が「営業」のキーワードで絞られているレコードの件数をカウントし、メッセージボックスに表示されます。

今回のVBAコードでは、ワークシート関数「SubTotal」を使っていますが、「SubTotal」関数の詳細は後日、別記事で紹介します。

SubTotal関数では、初めに集計方法を指定するための引数を設定するのですが、今回はデータ個数をカウントするための「3」を使っています。また、1行目の見出し行を除くため、最後で「-1」の処理を加えています。

Subtotal(3, Range(“C:C”)) – 1

Webs

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