【Excel VBA】特定の値以外のデータを抽出する2つの方法｜ワークシート全体・範囲指定に対応

この記事では、ExcelのVBAを使って、特定の値「以外」のデータを抽出する方法を詳しく解説します。ワークシート全体から抽出する方法と、特定のセル範囲だけを対象に抽出する方法の2パターンを、サンプルコード付きでわかりやすくご紹介します。

完成版コード（ワークシート全体から抽出する場合）

Sub Autofilter_Values_from_Whole_Worksheet()

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

特定の値以外を抽出する2つの方法

それでは本題に入りましょう。まずはワークシート全体から値を抽出する方法、続いて特定のセル範囲から抽出する方法の順に説明します。

方法1：ワークシート全体から特定の値以外を抽出する

まずは、Excel VBAを使って、ワークシート全体から特定の値と等しくないデータを抽出する方法を見ていきましょう。

ここでは、Sheet1に受験生の名前、試験の点数、そして成績（グレード）が入力されたデータセットを用意しました。データはセルA1から始まっています。

目標は、成績がF以外の学生だけを、Sheet2に抽出することです。

⧪ ステップ1：入力値を設定する

最初に、コードに必要な入力値を設定します。具体的には、元データのシート名（Sheet1）、抽出先のシート名（Sheet2）、抽出対象の列（1列目と3列目）、判定に使う列（3列目）、そして除外したい値（F）です。

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

⧪ ステップ2：Forループで条件に合う行を転記する

次に、Forループを使って元シートの各行を走査し、条件に一致しない（指定値と等しくない）行だけを、抽出先のシートへ書き込んでいきます。

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

以上をまとめると、完全なVBAコードは次のようになります。

⧭ VBAコード：

Sub Autofilter_Values_from_Whole_Worksheet()

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

⧭ 実行結果：

コード内の入力値を自分の環境に合わせて変更してから実行してください。注意点として、抽出先のシート（Sheet2など）は事前に作成しておく必要があります。存在しないシート名を指定するとエラーが発生します。

実行すると、指定した列（この例では1列目と3列目）のデータのうち、特定の値（この例ではF）と等しくない行だけが、抽出先のシートに出力されます。

方法2：特定のセル範囲から特定の値以外を抽出する

次に、ワークシート全体ではなく、指定したセル範囲だけを対象に抽出する方法を解説します。

今度はSheet3に同じく学生の名前、点数、成績のデータがありますが、今回はデータセットがセルB3からD15までの範囲に入力されています。

今回の目標は、成績がF以外の学生を、同じシート内のセルF3を起点として抽出することです。

⧪ ステップ1：入力値を設定する

まず、コードに必要な入力値を設定します。今回は元データのシート名（Sheet3）、元データの範囲（B3:D15）、抽出先のシート名（Sheet3）、抽出先のセル（F3）、抽出対象の列（1列目と3列目）、判定に使う列（3列目）、そして除外したい値（F）を指定します。

Source_Worksheet = "Sheet3"
Source_Range = "B3:D15"

Destination_Worksheet = "Sheet3"
Destination_Cell = "F3"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

⧪ ステップ2：Forループで条件に合う行を転記する

先ほどと同様に、Forループで範囲内の各行をチェックし、条件に合う行だけを出力先へ書き込みます。

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).Range(Source_Range).Rows.Count
    If Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Range(Destination_Cell).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

まとめると、完全なVBAコードは以下の通りです。

⧭ VBAコード：

Sub Autofilter_Values_from_Specific_Range()

Source_Worksheet = "Sheet3"
Source_Range = "B3:D15"

Destination_Worksheet = "Sheet3"
Destination_Cell = "F3"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).Range(Source_Range).Rows.Count
    If Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Range(Destination_Cell).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

⧭ 実行結果：

入力値を変更してからコードを実行してください（抽出先が別シートの場合は、必ずそのシートを先に作成しておきましょう。そうしないとエラーになります）。

実行すると、データセットの指定列（この例では1列目と3列目）のうち、特定の値（この例ではF）と等しくない行だけが、抽出先シートの指定セル（この例ではF3）以降に出力されます。

注意点

新しいワークシートへ抽出するコードを実行する前に、必ずそのワークシートを作成しておいてください。存在しないシートを指定するとエラーが発生します。

また、ワークシート全体から抽出する際には、VBAのUsedRangeプロパティを使用しています。UsedRangeは、シート上で実際に使用されているセル範囲を取得するための便利なプロパティなので、覚えておくとさまざまな場面で役立ちます。

まとめ

今回は、ExcelのVBAを使って、特定の値と等しくないデータを抽出する方法を2つ紹介しました。ワークシート全体を対象にする場合も、特定のセル範囲を対象にする場合も、基本となるのは「Forループで各行を判定し、条件に合う行だけを書き出す」という考え方です。

ご不明な点があれば、お気軽にお問い合わせください。

関連記事

• Excel VBAでオートフィルターが有効かどうかを確認する4つの方法
• VBAオートフィルター：昇順（小さい順）で並べ替える3つの方法
• Excel VBAで可視行のみを抽出してコピーする方法
• Excel VBA：オートフィルターが存在する場合に解除する7つの方法