エクセルVBAでオートフィルタを使用して絞込んだデータを操作する方法についてご紹介します。
オートフィルタで絞込み後のデータ件数をカウント
オートフィルタでデータの絞込みをした後に、絞り込まれたデータが何件か知りたい場合があると思います。
下の例ではA列の分類を「魚」で絞込んでいます。
絞込み後のデータ数は4件あり、データ数をメッセージで表示させています。
絞込み後のデータをカウントする場合はWorksheetFunctionのSubtotal関数を使用してカウントします。カウントした値を変数「DataCnt」に入れて、ヘッダー分を引くためマイナス1して算出しています。
Dim DataCnt As Integer
'<絞込み後のデータ件数をカウント>
'オートフィルタで絞込み(1列目が「魚」)
ActiveSheet.Range("A1").AutoFilter 1, "魚"
'絞込み後の件数を変数に格納する
DataCnt = WorksheetFunction.Subtotal(3, Range("A1").CurrentRegion.Columns(1))
'データ件数をメッセージボックスに表示
MsgBox "絞込み後のデータ件数:" & DataCnt - 1
Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")
オートフィルタで絞込み後のデータをコピー
絞り込まれたデータをコピーして他のシートに貼り付ける作業をやってみたいと思います。
下の例ではSheet1でA列の分類を「魚」で絞り込んで、絞込み後のデータ範囲をコピーし、Sheet2のA1セルに貼り付けています。ヘッダーと魚のデータ4行だけがコピーされていることが分かります。
「Range(“A1”).CurrentRegion」でA1セルのカレント範囲(見えている行のみ)を指定してコピーしています。
'<絞込み後のデータ範囲をコピー>
'オートフィルターで絞込み(1列目が「魚」)
ActiveSheet.Range("A1").AutoFilter 1, "魚"
'絞り込んだ結果をコピーし、Sheet2に貼り付け
ActiveSheet.Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")
オートフィルタで絞り込んだ行をループし操作する
オートフィルタで絞り込まれている状態で見えている(選択されている)行のみをループして操作したい場合についての説明です。
下の例ではまずC列の販売数を100以上で絞り込んでいます。
2行目、6行目、7行目、10行目、11行目が選択されている状態です。
このフィルタで選択されている行を対象にループ処理を行い、商品名を取得してメッセージボックスに表示させてみたいと思います。
Sub Sample()
Dim b As Range
Dim str As String
'販売数が100よりも大きいデータで絞込み
ActiveSheet.Range(Cells(1, 1), Cells(13, 3)).AutoFilter 3, ">100"
'A1セルから始まる表の範囲を対象とする
With ActiveSheet.Range("A1").CurrentRegion.Offset(1, 0)
'絞り込んだ行の範囲を変数「b」に代入する
For Each b In .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
'変数「b」の行範囲の左端から2列目の値を変数に格納し、うしろにカンマをつける
str = str & b.Cells(1, 2) & ","
Next b
End With
'メッセージを表示
MsgBox str
End Sub
▽実行後に表示されるメッセージボックス
フィルタで選択されている行の商品名「ラーメン,いわし,サバ,にんじん,玉ねぎ」がメッセージに表示されました。
オートフィルタについての関連記事
オートフィルタが設定されているか判定・すべて選択する方法↓
オートフィルタで絞り込む方法について↓
オートフィルタを使用したデータの並び替えについて↓
※コードのコピー利用について
・コードのコピーは自由におこなっていただけます。
・気を付けて作成はしていますがコードには誤りがある可能性があります。
・自身の環境で動作確認をしていますが、すべての方の環境で同様に動くことは保証できません。
・データの破損等の責任は負いかねますのでご自身の責任のもとお使いください。