- AutoFilterで抽出した行だけを別シートへ移したい
- 該当行がないとSpecialCellsで1004エラーになる
見出し付きの表を対象に、条件指定、可視行のコピー、解除と削除の順で説明します。削除例は実行前にブックを複製してください。
VBA全体の学習順を確認したい場合は、Excel VBA入門ガイドから読めます。
全体像:どの方法を使うか
AutoFilterは表の行を条件で表示・非表示にする機能です。条件を付ける操作と、可視行をコピー・削除する操作を分けると確認しやすくなります。
目的から選ぶ
- 表を絞り込む:見出しを含むRangeにAutoFilterを設定する
- 抽出結果を移す:SpecialCells(xlCellTypeVisible)で可視セルを取る
- 絞り込みだけを戻す:FilterModeを確認してShowAllDataを呼ぶ
- フィルター自体を外す:AutoFilterModeをFalseにする
AutoFilterで条件を指定する
単一条件と複数条件
Sheet1のA1:C列を、A列が状態、B列が担当、C列が金額の表として使います。1行目は見出し、空白行はない前提です。
Option Explicit
Public Sub FilterOrders()
Dim lastRow As Long
Dim area As Range
lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set area = Sheet1.Range("A1:C" & lastRow)
If Sheet1.AutoFilterMode Then Sheet1.AutoFilterMode = False
area.AutoFilter Field:=1, Criteria1:="処理中"
area.AutoFilter Field:=3, Criteria1:=">=1000", _
Operator:=xlAnd, Criteria2:="<=5000"
End Sub同じ列で2つの条件をANDにする場合はxlAnd、どちらか一方ならxlOrを指定します。複数の文字列から選ぶ場合はCriteria1:=Array(“処理中”, “保留”), Operator:=xlFilterValuesを使います。
解除と再表示の違い
AutoFilterMode = False はフィルターの矢印ごと外します。ShowAllDataは絞り込みを解除して全行を表示しますが、絞り込み中でないとエラーになる場合があるため、FilterModeを確かめてから呼びます。
Option Explicit
Public Sub ShowAllOrders()
If Sheet1.FilterMode Then Sheet1.ShowAllData
End Sub抽出した行だけコピーする
可視データ行がない場合を先に判定
Sheet2のA:C列はクリアしてから書き込みます。見出し行だけは常に可視なので、可視セルの判定とは別に、データ行があるかをSUBTOTALで確認します。
Option Explicit
Public Sub CopyVisibleOrders()
Dim lastRow As Long
Dim area As Range
Dim visible As Range
lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set area = Sheet1.Range("A1:C" & lastRow)
If Application.WorksheetFunction.Subtotal(103, Sheet1.Range("A2:A" & lastRow)) = 0 Then Exit Sub
On Error Resume Next
Set visible = area.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If visible Is Nothing Then Exit Sub
Sheet2.Range("A:C").ClearContents
visible.Copy Destination:=Sheet2.Range("A1")
Application.CutCopyMode = False
End SubSUBTOTALの103は非表示行を除いた空白でないセル数を数えます。A列のデータが空白にならない表を前提にしています。SpecialCellsは可視セルがない場合に1004エラーになるため、取得後もNothingを確認します。
抽出したデータ行を削除する
見出しを残して削除する
次のSubはSheet1で現在絞り込まれている可視データ行を削除します。表以外の行に影響しないよう、対象範囲を確認してから実行してください。
Option Explicit
Public Sub DeleteVisibleOrders()
Dim lastRow As Long
Dim dataRows As Range
Dim visible As Range
If Not Sheet1.FilterMode Then Exit Sub
lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set dataRows = Sheet1.Range("A2:C" & lastRow)
If Application.WorksheetFunction.Subtotal(103, Sheet1.Range("A2:A" & lastRow)) = 0 Then Exit Sub
On Error Resume Next
Set visible = dataRows.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If visible Is Nothing Then Exit Sub
visible.EntireRow.Delete
End SubEntireRow.Deleteはシート全体の行を削除します。表の横に別データがある場合は使わず、削除対象を別シートへ退避して処理してください。
実務例:処理中の注文だけを別シートに渡す
処理を3段階に分ける
実務ではフィルターの設定、抽出結果の確認、コピーを1つの作業として扱います。最初に見出しと最終行を確かめ、対象の表だけにAutoFilterをかけます。
次に可視データ行が1件以上あるかを調べます。見出し行は条件に一致するデータが0件でも可視のままなので、SpecialCellsが返るかどうかだけでは「該当あり」を判断できません。
最後に抽出結果をSheet2へコピーします。Sheet2のA:C列を消す構成なので、他のデータが入ったシートにはそのまま使わないでください。
抽出結果を帳票として残す場合は、コピー先のシートを確認してからVBAでPDF出力する方法へ進めます。
フィルターからコピーまでの通しサンプル
Option Explicit
Public Sub ExportPendingRows()
Dim lastRow As Long
Dim area As Range
Dim visible As Range
lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set area = Sheet1.Range("A1:C" & lastRow)
If Sheet1.AutoFilterMode Then Sheet1.AutoFilterMode = False
area.AutoFilter Field:=1, Criteria1:="処理中"
If Application.WorksheetFunction.Subtotal(103, Sheet1.Range("A2:A" & lastRow)) = 0 Then
MsgBox "該当するデータ行はありません"
Exit Sub
End If
On Error Resume Next
Set visible = area.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If visible Is Nothing Then Exit Sub
Sheet2.Range("A:C").ClearContents
visible.Copy Destination:=Sheet2.Range("A1")
Application.CutCopyMode = False
End Subこの例は抽出結果を見出し付きで移します。Sheet1にはフィルターが残るので、処理後に画面で確認し、必要ならShowAllDataで全行を表示してください。
途中で該当なしになったときはSheet2を消していません。前回の結果が残る可能性があるので、運用時には「該当なし」の表示とSheet2の扱いを明確にします。
複数値・範囲条件の選び方
同じ列で「処理中」または「保留」を表示するなら、Criteria1に配列を渡し、Operator:=xlFilterValuesを指定します。条件が2つだけでORにしたいならCriteria1とCriteria2に値を入れ、Operator:=xlOrを指定する方法もあります。
金額列の1000以上かつ5000以下のような範囲は、Criteria1とCriteria2をxlAndで結びます。別の列にも条件をかける場合は、同じ表のRangeへField番号を変えてAutoFilterを呼びます。
Fieldはワークシートの列番号ではなく、指定した表の左端から数えた列番号です。表がC列から始まるなら、Field:=1はC列を指します。
よくあるエラーと対処
1004:SpecialCellsで可視セルが取れない
SpecialCellsは条件に合うセルがないと実行時エラー1004になる場合があります。データ行の件数を先に確認し、必要な狭い範囲だけOn Error Resume Nextで取得します。
エラーを無視したまま後続処理へ進むと、Nothingのままコピーを試みることがあります。On Error GoTo 0で通常のエラー処理へ戻し、visible Is Nothingを確認してください。
ShowAllDataでエラーになる
フィルターの矢印があるだけで絞り込みが行われていない場合、ShowAllDataが使えないことがあります。FilterModeがTrueかを見てから呼ぶ形にします。
AutoFilterModeは矢印の有無を表し、FilterModeは絞り込み状態を表します。見た目が似た名前でも用途が違うため、解除したい対象で使い分けます。
表の横まで行が消えた
EntireRow.Deleteは選んだセルのある行をシート全体から削除します。表の右側に別データがある場合、そのデータも移動します。
削除例は、1行全体が1件のレコードであるシートに限ってください。表だけを残したい場合は、残す行を別シートへコピーして表を入れ替える方法を検討します。
よくある質問
フィルターの矢印だけを消すには?
AutoFilterModeをFalseにします。条件を解除して全行を表示するだけなら、FilterModeを確認したうえでShowAllDataを呼びます。
抽出結果が0件でも見出しは残りますか?
通常、見出し行はフィルターの対象データではないため表示されます。見出しが見えることをデータ行がある証拠にせず、A2以降を別に数えます。
SUBTOTALの103を使う理由は?
103は可視の空白でないセルを数える指定です。この例はA列に必ず値がある表を前提にしているため、抽出されたデータ行数の判定に使っています。
可視行を削除した後にフィルターはどうなりますか?
削除後もフィルター設定が残る場合があります。削除の結果を確認し、必要ならFilterModeを見てShowAllData、またはAutoFilterModeをFalseにします。
見出しが2行ある表でも同じコードを使えますか?
そのままでは使えません。AutoFilterをかける範囲の先頭行を見出しにし、データ範囲とSUBTOTALの開始行を合わせて変更します。
まとめ:抽出と可視行の処理を分ける
AutoFilterは見出し付きの表へ設定し、Fieldと条件の対象列を確認します。複数値には配列、2条件の組み合わせにはxlAndやxlOrを使えます。
可視行のコピーや削除では、該当データ行があるか先に判定します。特にEntireRow.Deleteはシート全体に影響するため、複製したブックで確認してから使ってください。
関連記事
あわせて読みたい記事




