VBA フィルター後の最終行は End(xlUp) だけでは危険｜可視セルで取る 3 解法

VBA でフィルター適用後のリストをループ処理すると、ws.Cells(ws.Rows.Count, "A").End(xlUp).Row で末尾行を取っているのに、なぜか件数が合わない。実機で確かめると、End(xlUp) はフィルターで非表示になった末尾側の行を見落とすことがあります。一方で、AutoFilter.Range の外にある追記行はフィルター中でも普通に取得します。本記事はこの分かりにくい挙動を Excel 2024 build 19929 で実機実測し、用途別に最終行・件数・範囲を取り分ける 3 解法と、よく踏む 4 つの落とし穴を整理します。

累計 272 計測値の検証ログから、初心者解説でよく見る .SpecialCells(...).Rows.Count - 1 や r.Item(r.Count).Row といった記法が、Areas の分かれ方で結果が変わってしまうことも確認できました。コピペで使える VBA サブルーチンと、配布 xlsm に同梱した検証マクロで読者の手元でも追試できる構成にしています。

解法早見表 — 用途別に最終行・件数・範囲を取り分ける

「最終行の行番号」「可視セルの件数」「フィルター範囲全体」は、それぞれ違う API で取ります。同じ SpecialCells(xlCellTypeVisible) でも、何を返したいかで書き方が変わるため、まず用途で選んでください。

解 1: SpecialCells(xlCellTypeVisible) で末尾可視行を取得する正しい書き方

末尾の可視行を取りたい場合は SpecialCells(xlCellTypeVisible) で可視セルの集合を取り、その Areas プロパティの末尾ブロックから行番号を取り出します。フィルターで可視範囲が複数ブロックに分かれていても、確実に「いちばん下の可視行」を返せる書き方です。

' 末尾可視行の行番号を取得する関数
' 引数 targetRange には「ヘッダーを除いたデータ範囲」を渡してください
' （ヘッダー込みだとフィルター 0 件のときヘッダーの行番号を返してしまうため。詳細は本記事「フィルター結果ゼロ件・空シートでのエラー処理」節）
Public Function GetLastVisibleRow(ByVal targetRange As Range) As Long
    Dim visibleAreas As Range
    On Error Resume Next
    Set visibleAreas = targetRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If visibleAreas Is Nothing Then
        GetLastVisibleRow = 0   ' 可視セルなし（フィルター結果ゼロ件など）
        Exit Function
    End If
    Dim lastArea As Range
    Set lastArea = visibleAreas.Areas(visibleAreas.Areas.Count)
    GetLastVisibleRow = lastArea.Rows(lastArea.Rows.Count).Row
End Function

' 呼び出し側（必ず Worksheet を明示し、ヘッダー除外で渡す）
Public Sub PrintLastVisibleRow()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim r As Long
    r = GetLastVisibleRow(ws.Range("A2:A101"))   ' ヘッダー A1 を除外
    If r = 0 Then
        Debug.Print "可視セルなし（フィルター結果 0 件など）"
    Else
        Debug.Print "末尾可視行: " & r
    End If
End Sub

Areas 末尾の取り方を 1 行ずつ分解する

visibleAreas.Areas(visibleAreas.Areas.Count) の部分が分かりにくいので、2 段で読み解きます。

• visibleAreas.Areas は、可視セルの連続ブロックの一覧です。フィルター適用中に「ヘッダー A1」と「A72:A101」が表示されていれば、Areas は 2 つ（A1 と A72:A101）になります。
• visibleAreas.Areas.Count は、その総数です。上記の例では 2。
• visibleAreas.Areas(2) で「末尾ブロック」（A72:A101）を取り、その中の最終行 .Rows(.Rows.Count).Row = 101 を返します。

一見冗長ですが、可視範囲が 1 ブロックでも複数ブロックでも同じコードで末尾可視行を返せるのがこの書き方の利点です。

よく見る短縮形 r.Item(r.Count).Row が壊れるケース

解説サイトでときどき見かける r.Item(r.Count).Row のような短縮形は、Areas が 1 つの場合だけ正解と一致し、Areas が複数になると連番カウントの順序で予期しないセルを返します。

件数が 50 件でも r.Item(50) は「Item 順の 50 番目」を指すため、Areas 構造によっては A100 ではなく A51 を返します。本記事の GetLastVisibleRow 関数のように、Areas 末尾ブロックから取り直してください。

落とし穴: 単セル指定の SpecialCells(xlCellTypeVisible) はシート全体可視範囲に展開される

もうひとつ、本記事の核心となる罠です。ws.Range("A1").SpecialCells(xlCellTypeVisible) のように単セルを起点に xlCellTypeVisible を呼ぶと、シート上に非可視範囲があるかどうかで戻り値が大きく変わります。

つまり、フィルター中のシートで SpecialCells(xlCellTypeVisible) を単セル起点で呼ぶと、A1 だけ取りたかったはずが 17 億セル超の可視範囲を返します。後続処理が For Each cell In r のようなループだと、止まらないか、Excel ごと落ちます。

このため、上記の GetLastVisibleRow 関数は引数に 必ず複数セルの Range を渡してください。ws.Range("A2:A101") のようにヘッダーを除外した複数セル範囲を指定すれば、シート全体扱いには展開されず、後述するヘッダー込み範囲のフィルター 0 件誤動作も同時に避けられます。

解 2: Subtotal(3, …) で件数だけを数える — 3 と 103 の使い分け

「行番号は要らないが件数を知りたい」場合は、WorksheetFunction.Subtotal が確実です。第 1 引数の関数番号によって、フィルター除外だけを行うか、手動非表示も除外するかが切り替わります。

Public Sub PrintVisibleCount()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ' 関数番号 3 = COUNTA、フィルターは除外、手動 Hidden は含む
    Debug.Print "Subtotal(3):   " & WorksheetFunction.Subtotal(3, ws.Range("A2:A101"))
    ' 関数番号 103 = COUNTA、フィルター + 手動 Hidden の両方を除外
    Debug.Print "Subtotal(103): " & WorksheetFunction.Subtotal(103, ws.Range("A2:A101"))
End Sub

同じ「COUNTA」でも 3 と 103 で振る舞いが変わるのは、手動で行を Hidden にした場合に差が出ます。フィルターを使っているか手動非表示を使っているかで、関数番号を選んでください。

合計を取りたい場合も同じ規則です。Subtotal(9, ...) は SUM（フィルター除外のみ）、Subtotal(109, ...) は SUM（フィルター + 手動 Hide 除外）。本記事の検証 B 列で実測すると、手動 Hide A50:A60 のとき 9 が 5900、109 が 5350 と、550 の差が出ました。

解 3: AutoFilter.Range 経由でフィルター範囲だけに限定する

「フィルターを当てている範囲だけ取りたい」「ヘッダー行は除外したい」というときは、ActiveSheet.AutoFilter.Range から直接組み立てます。シート全体に SpecialCells を投げるより、対象範囲を絞れる分だけ安全です。

Public Function GetFilteredVisibleRange(ByVal ws As Worksheet) As Range
    ' AutoFilter が無い場合のガード（FAQ Q-4 参照）
    If ws.AutoFilterMode = False Then
        Set GetFilteredVisibleRange = Nothing
        Exit Function
    End If
    Dim filteredRange As Range
    On Error Resume Next
    With ws.AutoFilter
        ' ヘッダー除外 + 対象列 1 列だけに絞る
        Set filteredRange = .Range.Offset(1).Resize(.Range.Rows.Count - 1).Columns(1) _
                              .SpecialCells(xlCellTypeVisible)
    End With
    On Error GoTo 0
    Set GetFilteredVisibleRange = filteredRange
End Function

.Columns(1) でキー列 1 列だけに絞っているのがポイントです。これを書かないと AutoFilter.Range は複数列を返してしまい、後段の .Count は「行数」ではなく「セル数」になってしまいます。配布 xlsm のシート 2 に、対象列を絞った例と絞らなかった例の両方を用意しています。

罠 1: End(xlUp) はフィルターで非表示になった末尾行を見落とす — 手動非表示とは挙動が違う

本記事でいちばん事故りやすいのが End(xlUp) です。「フィルターを当てると End(xlUp) はおかしくなる」と書いてある記事もありますが、より正確にはフィルターで非表示になった末尾側の行だけを見落とします。フィルターが触っていない範囲のデータは普通に取りに来ます。

つまり、End(xlUp) は次の 3 通りの挙動を取ります。

• フィルターで非表示になった行: 上方向の探索で飛ばされ、見落とす。
• AutoFilter.Range の外にある行: フィルターと無関係なので、普通に取得する。
• 手動 Hide だけの行（フィルターなし）: 非表示を通過して、データの最下段で止まる。

「フィルター中のループで件数が合わない」と感じたら、まず AutoFilter.Range の中に末尾データが含まれているか、含まれているならフィルターで非表示になっているかを確認してください。配布 xlsm のシート 3 に同じ条件の再現ブックを入れています。

VBA でフィルター後の各可視行に同じ処理を一括で当てたい場合は、可視セルの集合を一度コピーして別シートに展開する書き方が安全です。実装例は「【Excel VBA】条件に合う行だけ別シートにコピーするマクロ」が参考になります。

罠 2: SpecialCells.Rows.Count は可視行数ではない — Areas 分割で件数がずれる

初心者解説でよく見る r.SpecialCells(xlCellTypeVisible).Rows.Count - 1 という件数の数え方は、Areas が複数に分かれると正しい値を返しません。実機で計測すると、.Rows.Count は「可視セル全体の行数」ではなく「先頭 Area の行数」と一致します。

すべて 1 つの規則「Rows.Count = 先頭 Area の行数」で説明できます。範囲指定（ヘッダーを含めるかどうか、開始セルをどこにするか）で先頭 Area の構造が変わるため、同じデータでも .Rows.Count の値が変わります。

可視セル件数を取りたいなら、次のどちらかを使ってください。

' 可視セルの総数（フィルター後の件数）
Dim r As Range
Set r = ws.Range("A2:A101").SpecialCells(xlCellTypeVisible)
Debug.Print "件数: " & r.Count          ' OK（または .Cells.Count）
Debug.Print "誤り: " & r.Rows.Count     ' NG（先頭 Area の行数）

.Rows.Count は Excel の不具合ではなく、「Range オブジェクトの行数」を返す仕様どおりの動きです。可視セル件数として使えないだけで、間違った値というわけではないため、本記事では「仕様上の落とし穴」と表現しています。

フィルター結果ゼロ件・空シートでのエラー処理

フィルターを当てて結果がゼロ件になると、SpecialCells(xlCellTypeVisible) は 実行時エラー 1004「該当するセルが見つかりません」 になります。ループの内側で起きるとマクロ全体が止まるので、On Error Resume Next で受け止めるのが定番です。

Public Function CountVisibleSafely(ByVal target As Range) As Long
    Dim r As Range
    On Error Resume Next
    Set r = target.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If r Is Nothing Then
        CountVisibleSafely = 0
    Else
        CountVisibleSafely = r.Count
    End If
End Function

ただし、引数の渡し方によってはエラーにならず「ヘッダー 1 セルだけ可視」と判定されることがあるため、注意してください。

「フィルター 0 件のはずがマクロが普通に終わってしまう」原因の多くがこのヘッダー込み範囲です。GetLastVisibleRow や件数取得関数には、必ずヘッダーを除いた範囲を渡してください。エラーチェックも合わせて、上の CountVisibleSafely パターンで包んでおくのが安全です。

よくある質問

Q-1 フィルターなしのシートでも 3 解法は使える？

使えます。フィルターが当たっていないシートでは SpecialCells(xlCellTypeVisible) は単に「シート上の可視セル」を返すので、解 1 と解 2 はそのまま使えます。解 3（AutoFilter.Range 経由）はフィルター適用前提なので、ws.AutoFilterMode = False のときは Nothing を返すようにガードしてください（本記事の GetFilteredVisibleRange 参照）。

Q-2 Excel 2019 / Mac 版 / Web 版でも全部動く？

本記事の検証は Windows 11 Pro 25H2 + Excel 16.0 build 19929（Office 2024 永続版）で行っています。Mac 版 Excel、Excel for the web、Excel 2019 / 2021 以前は未検証です。SpecialCells や Subtotal 自体は古くからある API ですが、Areas の構造や .Rows.Count の挙動は Office 更新で細部が変わる可能性があります。配布 xlsm のシート 2 に検証マクロを入れているので、お手元の環境でも追試できます。

Q-3 テーブル機能（ListObject）でフィルターしているときも同じ？

ListObject（Excel テーブル）でも SpecialCells(xlCellTypeVisible) と Areas 末尾ブロックの考え方はそのまま流用できます。ただし End(xlUp) はテーブル末尾行が非表示のときに通常範囲と結果がずれるケースを実測で確認しているため（例: 偶数行のみフィルター可視で末端 A51 が非表示のとき、通常範囲では End(xlUp)=50 ですが ListObject では End(xlUp)=51）、テーブルでは lo.DataBodyRange を起点に SpecialCells で取るのが安全です。本記事の GetLastVisibleRow 関数は lo.DataBodyRange を渡せばそのまま動きます。

Q-4 AutoFilter が無いとき・空テーブルのときはどうガードする？

解 3 を呼ぶ前に次のチェックを入れてください。ws.AutoFilterMode = False なら Nothing を返す、ws.FilterMode = False なら絞り込みが当たっていない、ListObject なら lo.DataBodyRange Is Nothing でデータ行 0 件、SpecialCells の 1004 は On Error Resume Next で受ける。本記事の GetFilteredVisibleRange と CountVisibleSafely がそのテンプレです。

Q-5 単セル罠の検証で Count ではなく CountLarge を使う理由

フィルター中に単セル起点の SpecialCells(xlCellTypeVisible) を呼ぶと、可視範囲がシート全体に展開され、セル数が 17 億超になります。.Count は Long 型（最大約 21 億）なのでギリギリ収まりますが、列の幅やフィルター条件によっては 21 億を超えてオーバーフローします。.CountLarge は Variant/Double で返るためオーバーフローしません。本記事の検証スクリプトも CountLarge を使っています。

フィルター後の最終行・件数・範囲は、用途に応じて 3 解法を使い分け、End(xlUp) と .Rows.Count の落とし穴を避けるだけで、ほとんどの実務マクロは安定します。フィルター操作自体の UI 解説は「Excelオートフィルターの使い方｜設定・絞り込み・解除の基本」に、フィルター後のデータを別シートにコピーする実装例は「【Excel VBA】条件に合う行だけ別シートにコピーするマクロ」にまとめています。あわせて参考にしてください。