Office TANAKA - Excel VBA Tips[オートフィルタ[絞り込んだ行を操作する]]

大量のデータを絞り込むには、オートフィルタが便利です。ここでは、VBAからオートフィルタを使い倒すテクニックをご紹介します。なお、ボリュームがありますので、以下の項目にページを分けて解説します。なお、ここで解説するオートフィルタは、通常のワークシートに設定されているとします。テーブルでオートフィルタを操作するときは、また別の考え方が必要です。VBAでテーブルを扱う方法に関しては「VBAでテーブルの操作」をご覧ください。

書き方の基本
文字列で絞り込む
数値で絞り込む
日付で絞り込む
色で絞り込む
作業列で絞り込む
絞り込んだ結果をコピーする
絞り込んだ結果を集計する
絞り込んだ結果の行を操作する(←このページ)
オートフィルタの状況を判定する

行が特定できれば何でもできる

オートフィルタで絞り込んだ結果に対して何かをする。これ、実務では必須の作業です。絞り込んだ結果全体に対して、ひとつのことをしてやると、Excelがうまくやってくれて、見えているセルだけ処理してくれます。しかし、絞り込んだ結果を部分的に操作したり、絞り込んだ結果の、それぞれのセルや行に対して個別の操作をしようとすると、難易度が一気に上がります。たとえば、次のケースで確認してみましょう。

上図の表に対して「1列目を"田中"」で絞り込みます。まず、上記のように「全体に対してひとつのことをすると、Excelがうまくやってくれる」という動作を確認してみます。なお、ここからは、絞り込んだ結果のうちタイトル行を除くデータだけを対象にします。その考え方は「オートフィルタで絞り込んだデータだけ特定する」をご覧ください。

まずはセルの書式を設定してみましょう。"可視セル"選択をしていない点に留意してくださいね。

Sub Macro1()
    Range("A1").AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        .Resize(.Rows.Count - 1).Font.ColorIndex = 3
    End With
    Range("A1").AutoFilter
End Sub

ね、見えているセルだけ処理されたでしょ。次はコピーしてみましょうか。一般的には、別のシートにコピーすることが多いですけど、ここでは見やすくするために、同じシート内でコピーします。

Sub Macro2()
    Range("A1").AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        .Resize(.Rows.Count - 1).Copy Range("E2")
    End With
    Range("A1").AutoFilter
End Sub

これもうまくいきます。ちなみに、オートフィルタで絞り込んだ結果の「全体に対してひとつのことをすると、Excelがうまくやってくれる」という仕様は、Excel 2002からです。その前の、Excel 2000まではダメでした。こんなことをすると、隠れているセルまで全部処理されてしまいました。

さあ、本題はここからです。じゃあ、絞り込んだ結果を1行ずつ処理をするには、どうしたらいいでしょう。たとえば、次のような操作です。今度は、次のような表で考えます。

この表を「1列目を"田中"」で絞り込みます。

見えている行を、1行ずつ操作します。ここでは、B列からD列までの数値を合計してE列に代入するとしましょう。つまり、行ごとに、行内のセルをひとつずつ操作したいというケースです。

これ、もし操作対象の行が特定できたら、次のように操作できます。

Sub Macro3()
    Dim A As Range, S As Long, i As Long
    Set A = Range("A3:D3")
    For i = 2 To 4
        S = S + A.Cells(1, i)
    Next i
    MsgBox S
End Sub

A.Cells(1, i) のあたりで「なんじゃこりゃ～」的に感じる方もいるかもしれませんね。VBAは、こういうこともできるんですよ～がんばってスキルアップしてくださいねぇ～。まぁ、とにかく。操作対象の行を Set A = Range("A3:D3") みたく特定できたら、その行内のセルをひとつずつ操作できるということです。じゃ、オートフィルタで絞り込んだ結果のすべての行を1行ずつ特定するにはどうしたらいいのでしょうか。

まずは、絞り込んだ結果のRowsコレクションを調べてみましょう。なお、ここからは面倒くさいので「1列目を"田中"」で絞り込むところは割愛します。

Sub Macro4()
    With Range("A1").CurrentRegion.Offset(1, 0)
        MsgBox .Resize(.Rows.Count - 1).Rows.Count
    End With
End Sub

絞り込んだ結果、見えているのは4行です。その4行とは「3行目」「5行目」「6行目」「9行目」です。もし、絞り込んだ結果のRows.Countが「4」だったら、それらを順番に操作すればできそうです。結果は次のとおり。

「1」って何だよ「1」ってw。んなわきゃーねーだろって。まさか、先頭行しか認識してないとか？じゃ、その1行のアドレスを調べてみましょう。

Sub Macro4()
    With Range("A1").CurrentRegion.Offset(1, 0)
        MsgBox .Resize(.Rows.Count - 1).Rows.Address
    End With
End Sub

ああ、なるほどね。Rowsで取得できたのは、見えている行だけじゃなくて、オートフィルタ全体だったんですね。じゃ、ここで"可視セル"を使いましょう。

絞り込んだ結果の行を操作する

行が特定できれば何でもできる