VBAでオートフィルタを操作すると「実行時エラー」になる原因と対処法

XLSX/マクロ/

目次

はじめに：VBAでのオートフィルタ操作におけるエラーの背景

Excel業務の自動化において、特定の条件に合致するデータを抽出し、別のシートに転記・コピーする処理は最も頻繁に作成されるマクロの一つです。このデータ抽出において「オートフィルタ（AutoFilter）」機能は処理速度も速く、非常に有用な手段です。しかし、手作業でオートフィルタを操作する際には発生しないような様々なエラーが、VBA（マクロ）から操作した途端に頻発し、多くの開発者を悩ませます。

手作業の場合、Excelが自動的に空行を無視してくれたり、既にフィルタがかかっていれば警告を出さずに上書きしてくれたりと、ユーザーの意図を汲み取った「空気を読む」動作をしてくれます。しかし、VBAは記述されたコードを厳格に実行するため、シートの現在の状態（フィルタがすでにかかっているか、シートが保護されているか、抽出結果が0件ではないか）を事前に確認・制御せずにコマンドを投げると、即座に「実行時エラー」を返して処理を強制停止させます。

本記事では、VBAでオートフィルタを操作する際に発生する代表的な実行時エラーの原因を詳細に分解し、それぞれの正しい対処法を解説します。また、実務においてどのような状況で実行されてもエラーで止まらない、堅牢（Robust）なコードの書き方とベストプラクティスを網羅的に提供します。

オートフィルタで発生する2大「実行時エラー」

VBAでオートフィルタを操作する際、主に以下の2つの実行時エラーに直面します。

  • 実行時エラー ‘1004’: Range クラスの AutoFilter メソッドが失敗しました。
    （オートフィルタを「かける・解除する」というアクション自体が拒否された場合に発生）
  • 実行時エラー ’91’: オブジェクト変数または With ブロック変数が設定されていません。 または 該当するセルが見つかりません。
    （オートフィルタで絞り込んだ「結果（可視セル）」を取得して操作しようとした際に発生）

それぞれの詳細な原因と対処法を順に見ていきましょう。

実行時エラー ‘1004’（AutoFilterメソッドが失敗しました）の6つの原因と対処法

このエラーは「フィルタをかけようとしたが、シートやデータの状態がそれを許さなかった」時に発生します。

原因1：シートが保護されている（編集制限）

対象のシートに「シートの保護」がかかっている状態では、デフォルトでオートフィルタの操作は許可されていません。この状態で Range("A1").AutoFilter を実行するとエラー1004になります。

【対処法】
マクロの実行直前に一度シートの保護を解除するか、あるいはブックを開いた際（Workbook_Openイベント）に、マクロからの操作のみを許可する UserInterfaceOnly:=True という引数を指定して保護をかけ直します。

' マクロ実行時のみ保護を一時解除するアプローチ
ActiveSheet.Unprotect Password:="your_password"
' --- オートフィルタの処理 ---
ActiveSheet.Protect Password:="your_password"

原因2：既に別の範囲でオートフィルタが設定されている

Excelの仕様上、1つのシートに設定できるオートフィルタは1箇所のみです。例えば、A1:C10の範囲にすでにオートフィルタの矢印が表示されている状態で、全く別のセル範囲（例：E1:G10）に対して AutoFilter メソッドを実行しようとすると、メソッドが失敗します。

【対処法】
新たにフィルタをかける前に、シート上に既存のオートフィルタが存在する場合は必ず解除（リセット）する処理を先頭に入れます。（具体的なコードは後述の「ベストプラクティス」で解説します）

原因3：指定したField（列番号）が範囲外

オートフィルタの第1引数である Field は、「シート全体の列番号（A列=1, B列=2…）」ではなく、「指定したRange範囲の左端を1とした相対的な列番号」です。 例えば、Range("C1:E10").AutoFilter Field:=4, Criteria1:="東京" と指定した場合、C列からE列までは3列しか存在しないため、「4列目」を指定すると範囲外となりエラー1004が発生します。

【対処法】
Field に指定する数値が、ターゲットとしている Range オブジェクトの列数（Columns.Count）を超えていないか確認し、相対列番号で正しく指定します。

原因4：対象範囲が空、または結合セルが含まれている

抽出対象となるリストのヘッダー行（1行目）が空欄である場合や、リスト内に複雑な「セルの結合」が含まれている場合、VBAはどこを基準にフィルタをかければよいか認識できずエラーになります。

【対処法】
データベースの原則に従い、ヘッダー行（1行目）には必ず全ての列に項目名を入力し、データ範囲内のセルの結合は解除（または「選択範囲内で中央」などの代替フォーマットを使用）してください。

原因5：テーブル（ListObject）に対する不適切な操作

対象のデータ範囲が「Excelのテーブル機能（Ctrl+Tで作成したテーブル）」に変換されている場合、通常の Range.AutoFilter メソッドを使用するとエラーになることがあります。テーブルにはテーブル専用のフィルタ操作オブジェクトが存在します。

【対処法】
対象がテーブルである場合は、ListObjects コレクションから対象のテーブルを取得し、その Range.AutoFilter を操作します。

' テーブル「テーブル1」の2列目を「東京」で絞り込む
ActiveSheet.ListObjects("テーブル1").Range.AutoFilter Field:=2, Criteria1:="東京"

原因6：日付データの絞り込み（フォーマットの不一致）

直接的なエラー1004にはなりにくいですが、「エラーにはならないが、該当データがあるはずなのに抽出結果が0件になる」という事象を引き起こす最大の原因が「日付」の絞り込みです。VBAの日付型（Date）とシート上のシリアル値・表示形式の認識のズレが原因です。その後0件の状態でデータを操作しようとして、別のエラーを誘発します。

【対処法】
日付でフィルタをかける場合は、対象セルの「表示形式」と完全に一致する「文字列」に変換してから渡すか、演算子を使用してシリアル値の範囲で指定します。

' 例：表示形式が yyyy/mm/dd の場合
Dim targetDate As Date
targetDate = Date ' 今日
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Format(targetDate, "yyyy/mm/dd")

実行時エラー ’91’ または ‘1004’（該当するセルが見つかりません）の原因と対処法

フィルタをかけること自体には成功したものの、その後の処理でマクロが止まるケースです。

原因：抽出結果が0件の状態で可視セル（SpecialCells）を操作しようとした

オートフィルタでデータを絞り込んだ後、表示されているデータだけを別シートにコピーするために、SpecialCells(xlCellTypeVisible)（可視セルの取得）を使用するのがVBAのセオリーです。

しかし、フィルタの条件に合致するデータが1件も存在せず（ヘッダー行のみが表示されている状態）、かつデータ行を取得しようとした場合、VBAは「該当する（表示されているデータ）セルが存在しない」と判断し、エラーを発生させます。

対処法：結果件数を事前にカウントして分岐する

コピーや削除の処理を行う前に、抽出されたデータが「ヘッダー行以外に存在するかどうか」を判定するロジックを必ず組み込みます。これには Subtotal 関数を使用するのが最も確実で高速です。

Dim lastRow As Long
Dim visibleCount As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

' オートフィルタ実行
Range("A1:D" & lastRow).AutoFilter Field:=1, Criteria1:="対象外データ"

' SUBTOTAL関数(103=COUNTA)を使って、可視状態にあるA列のデータ件数を取得
' ヘッダー行(A1)を除外するため、A2以降をカウントする
visibleCount = WorksheetFunction.Subtotal(103, Range("A2:A" & lastRow))

If visibleCount > 0 Then
    ' データが1件以上ある場合のみ処理（コピーや削除）を行う
    Range("A2:D" & lastRow).SpecialCells(xlCellTypeVisible).Copy
    Worksheets("出力").Range("A2").PasteSpecial xlPasteValues
Else
    ' データが0件の場合は何もしない（またはメッセージを出す）
    MsgBox "該当するデータはありませんでした。"
End If

【実務向け】エラーを起こさないオートフィルタのベストプラクティス（正しい書き方）

上記の原因をすべて踏まえ、実務においてどのような状態のシートでマクロが実行されてもエラーで止まることのない、安全なVBAコードの書き方を解説します。

状態リセットの鉄則：AutoFilterModeとFilterModeの違い

オートフィルタをかける前に、現在のフィルタ状態をリセットすることは必須です。このリセット処理において、2つの似たプロパティを正しく使い分ける必要があります。

  • AutoFilterMode： シート上にオートフィルタの「矢印（ドロップダウン）」が存在するかどうかを示します。これを False にすると、矢印そのものが消え、完全な初期状態に戻ります。
  • FilterMode： オートフィルタの矢印が存在し、かつ「現在何らかの条件で絞り込まれてデータが隠れている状態」かどうかを示します。

エラーを回避するための最も安全な事前処理は、「矢印があるかどうかに関わらず、一度矢印を完全に消去して初期化する」ことです。つまり ActiveSheet.AutoFilterMode = False を実行します。これにより、予期せぬ場所にかかっていたフィルタや、前回の絞り込み条件を完全にクリアできます。

実務で使える堅牢なオートフィルタのテンプレートコード

以下は、あらゆるエラー要因を排除した、そのまま実務に組み込める「オートフィルタ抽出〜別シートへのコピー」のテンプレートコードです。

Sub SafeAutoFilterAndCopy()
    Dim wsData As Worksheet
    Dim wsDest As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim filterRange As Range
    Dim visibleCount As Long

    ' シートの設定
    Set wsData = ThisWorkbook.Worksheets("データ")
    Set wsDest = ThisWorkbook.Worksheets("出力")

    Application.ScreenUpdating = False ' 画面のちらつき防止

    With wsData
        ' 【重要1】既存のオートフィルタを完全に解除（リセット）する
        If .AutoFilterMode Then
            .AutoFilterMode = False
        End If

        ' データの最終行と最終列を取得
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        ' データがヘッダーのみ（1行しかない）場合は処理を終了
        If lastRow <= 1 Then
            MsgBox "処理対象のデータがありません。", vbExclamation
            GoTo ExitSub
        End If

        ' フィルタ対象範囲を明確にオブジェクトとしてセットする
        Set filterRange = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))

        ' 【重要2】明示的な範囲に対してオートフィルタを実行（例：2列目が"東京"）
        filterRange.AutoFilter Field:=2, Criteria1:="東京"

        ' 【重要3】絞り込み結果が0件でないか確認（ヘッダーを除いたA列をカウント）
        visibleCount = WorksheetFunction.Subtotal(103, .Range(.Cells(2, 1), .Cells(lastRow, 1)))

        If visibleCount > 0 Then
            ' 出力先シートの既存データをクリア（ヘッダーは残す）
            wsDest.Rows("2:" & wsDest.Rows.Count).ClearContents

            ' ヘッダー行を含まずに可視セルのみをコピーするための高度な指定
            ' Offset(1, 0)で1行下にずらし、Resizeで行数を1減らす
            filterRange.Offset(1, 0).Resize(filterRange.Rows.Count - 1, filterRange.Columns.Count) _
                       .SpecialCells(xlCellTypeVisible).Copy

            ' 出力先のA2セルに値のみ貼り付け
            wsDest.Range("A2").PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False

            MsgBox visibleCount & "件のデータを抽出しました。", vbInformation
        Else
            MsgBox "条件に一致するデータはありませんでした。", vbInformation
        End If

        ' 処理が終わったらフィルタを解除して元の状態に戻す
        .AutoFilterMode = False

    End With

ExitSub:
    Application.ScreenUpdating = True
End Sub

さらに安定させるための高度なテクニック

上記のテンプレートに加え、大規模なデータを扱う場合や、ユーザーが勝手に行を追加・削除する可能性がある環境では、以下の点にも配慮するとより完璧です。

  • End(xlUp)の落とし穴： lastRow = Cells(Rows.Count, 1).End(xlUp).Row は、A列が空欄であることを想定していません。A列に空欄があるデータベースの場合は、必ず「必ず値が入っている列（キー列）」を基準にして最終行を取得してください。
  • AdvancedFilter（フィルタオプション）の検討： 複数の列にまたがる複雑なOR条件（A列が”東京” または B列が”大阪”）を設定する場合、オートフィルタのVBAコードは非常に難解になり、配列を使うなどの工夫が必要になります。このような場合は、オートフィルタを諦め、Range.AdvancedFilter（フィルタオプション機能）を使用する方が、コードがシンプルになり実行速度も向上します。

まとめ：エラーの事前回避がVBAオートフィルタの要

VBAでオートフィルタを操作する際に発生する「実行時エラー1004」や「エラー91」は、VBAの不具合ではなく、「シートの現在の状態」と「実行しようとした命令」の間に矛盾が生じたために発生する当然の警告です。

これらのエラーを完全に防ぐためには、「手作業なら無意識に確認していること」をすべてコードとして記述してあげる必要があります。具体的には、「事前にAutoFilterModeをFalseにして初期化する」「操作対象のRangeを正確に指定する」「SpecialCellsで操作する前にSubtotalで抽出件数をカウントする」という3つの鉄則を守ることです。

本記事で紹介したテンプレートコードや考え方をベースにすることで、誰がいつ実行しても途中で止まることのない、堅牢で信頼性の高いデータ抽出マクロを構築することができるようになります。エラー処理を適切に実装し、オートフィルタの強力な処理速度を業務効率化に最大限活用してください。