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

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

ほとんどのケースは、行を特定しなくてもいい

オートフィルタは、絞り込んで終わりではありません。絞り込むことがゴールではなく、絞り込んだ後に、必ず何かの操作をします。たとえば、絞り込んだ結果をコピーしたり編集したり削除したりと。しかし、そうした操作のとき「オートフィルタで絞り込んだ結果"全体"に対して何かの操作をすると、Excelは"表示されているセル"だけを対象にする」という特性を理解していると、簡単に実現できます。たとえば、絞り込んだ結果を別のシートにコピーするやり方は「絞り込んだ結果をコピーする」を参照してください。ほかにも、いくつかやってみましょう。なお、ここでは、絞り込んだ結果の実データだけを操作します。そのやり方は「絞り込んだ結果をコピーする」の"自分でResizeしちゃう法"で記述します。もし"Excelおまかせ法"の方が分かりやすいのでしたら、そちらを使ってください。

A列を"田中"で絞り込みます。

Sub Macro1()
    Range("A1").AutoFilter 1, "田中"
End Sub

絞り込んだ結果の背景色を赤にします。

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

オートフィルタを解除します。

ね？うまくいくでしょ。今度は、実務でよくやる「"田中"の行だけ削除」をやってみます。

A列を"田中"で絞り込みます。

Sub Macro2()
    Range("A1").AutoFilter 1, "田中"
End Sub

絞り込んだ結果の行全体を削除します。

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

オートフィルタを解除します。

ね？うまくいくでしょ。可視セルなんて選択しなくても、Excelは「オートフィルタで絞り込んだ結果"全体"に対して何かの操作をすると、"表示されているセル"だけを対象にする」んです。

とはいえ、実務って一筋縄ではいきません。いろんなことを無茶ぶりされますw ときには、絞り込んだ"全体"に対してではなく、絞り込んだ結果を"セル単位"で何かしたいこともあります。ここでは、そのやり方を解説します。

表示されている行だけを特定する

まずは、表示されている行だけを個別に特定する方法です。A列を"田中"で絞り込み、その実データだけを特定します。

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

このままでは、非表示のセル(行)も含まれています。ここに何かの操作をすると、Excelが"見えているセルだけ"うまくやってくれるのですが、今回はそうしたExcelの"おまかせ"ではありません。VBAのコードで明示的に1行ずつ特定したいんです。なので、まずはこの範囲の表示されているセルだけを特定します。それには、SpecialCellsプロパティを使います。

Sub Macro3()
    Range("A1").AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select
    End With
End Sub

違いが分かりますか？今度は選択範囲が非連続になっています。この範囲で、行を個別に操作します。ここはFor Eachを使いましょう。For Eachの考え方は次のとおりです。

「行」というのはFor Eachで使う制御変数です。特に深い意味はありませんが、行ですから「Row」の頭文字である「R」にしましょうか。

さて「表示されているすべてのセル」が

With Range("A1").CurrentRegion.Offset(1, 0)
    .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With

でしたよね。今回欲しいのは「すべてのセル」じゃなく、その中にある「すべての行」です。そこで、次のようにします。

最後の「Rows」がポイントです。なお、ここで使う制御変数「R」はRange型で宣言します。Row型というオブジェクト型はありません。ここまでを確認してみましょう。

Sub Macro4()
    Dim R As Range
    Range("A1").AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        For Each R In .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
            Debug.Print R.Address
        Next R
    End With
End Sub

うまくいきました。あとは、この行内のセルを個別に操作してやればいいです。

RangeとCellsの使い方

行を特定することはできました。あとは、行内のセルをひとつずつ指定する方法です。それには、RangeとCellsの、ちょっと変わった使い方がポイントです。

ワークシート全体に対して、RangeやCellsを使うと、次のようになります。

Sub Macro5()
    ActiveSheet.Range("B2") = "○"
    ActiveSheet.Cells(4, 3) = "×"
End Sub

これはいいですね。では、RangeやCellsを特定のセル範囲に対して使ってみましょう。

上図で選択しているのは、セル範囲C4:F9です。ここに対してRangeとCellsを使ってみます。

Sub Macro6()
    Range("C4:F9").Range("B2") = "○"
    Range("C4:F9").Cells(4, 3) = "×"
End Sub

分かりますか？RangeとCellsは、特定のセル範囲に対して使うと、そのセル範囲での位置を表します。

これは、ここで指定したセル範囲C4:F9を、ひとつのワークシートに見立てたようなイメージです。

さあ、よろしいですか？では、いよいよ、オートフィルタで絞り込んだ行内のセルを操作する考え方です。オートフィルタによって表示されているセル範囲から、行をひとつずつ特定します。さっきのFor Eachによれば、その行は、制御変数「R」で表されるんでしたよね。

これは、すべての行について同じことです。

ケース1：行内のセルを計算する

それでは、いくつかの例をお見せしましょう。まずは、下図のような表があったとします。

この表で、A列が"田中"の行だけ、E列に数値1～数値3の合計を代入したいです。こんな感じに。

この場合、すべての行で、次のように考えます。

Sub Macro7()
    Dim R As Range
    Range("A1").AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        For Each R In .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
            R.Range("E1") = WorksheetFunction.Sum(R.Range("B1:D1"))
        Next R
    End With
    Range("A1").AutoFilter
End Sub

ケース2："田中"だけ連番を振る

これ、VBAのセミナーでだいたい半年に1回くらいのペースで質問されますw たとえば下図のような表で

A列が"田中"の行にだけ連番を振りたいと。下図みたいに。

ここまでの解説を、よ～く読んで、ちゃ～んと理解してくださいね。そしたら、何でもできます。もう詳細な解説はしません。

Sub Macro8()
    Dim R As Range, N As Long
    Range("A1").AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        For Each R In .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
            N = N + 1
            R.Range("C1") = N
        Next R
    End With
    Range("A1").AutoFilter
End Sub

ケース3：セルを書き換える

上図を下図のようにします。

Sub Macro9()
    Dim R As Range
    Range("A1").AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        For Each R In .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
            R.Range("B1") = R.Range("B1") & "-" & R.Range("C1")
        Next R
    End With
    Range("A1").AutoFilter
End Sub