VBAでExcelの重複した値を削除する

このチュートリアルでは、VBAのRemoveDuplicatesメソッドを使用して、重複を削除する方法を説明します。

RemoveDuplicatesメソッド

Excelのワークシートにデータをインポートしたり貼り付けたりすると、値が重複していることがよくあります。 入力されたデータをクリーニングし、重複を削除する必要があるかもしれません。 幸いなことに、VBAのRangeオブジェクトの中に、これを簡単に実行するメソッドがあります。

Range("A1:C8").RemoveDuplicates Columns:=1, Header:=xlYes

構文は以下のとおりです。

RemoveDuplicates([Columns],[Header])

• [Columns] – 値が重複していないかチェックする列を指定します。指定したすべての列の値が一致した場合に重複とみなされます。
• [Header] – データにヘッダーがあるかを指定します。 xlNo (デフォルト)、xlYes、xlYesNoGuess

技術的には、どちらのパラメータもオプションです。しかし、Columns引数を指定しない場合、重複は除去されません。

Headerのデフォルト値はxlNoです。もちろんこの引数を指定した方が良いのですが、ヘッダー行がある場合、ヘッダー行が重複としてマッチする可能性は低いです。

RemoveDuplicatesの使用上の注意

• RemoveDuplicates メソッドを使用する前に、使用する範囲を指定する必要があります。
• RemoveDuplicates メソッドは、重複が見つかった行をすべて削除しますが、すべての値を含む元の行は保持します。
• RemoveDuplicates メソッドは列に対してのみ機能し、行に対しては機能しませんが、VBA コードを記述してこの状況を修正することができます (後述)。

VBAの例で使用するサンプル データ

コードで動作を説明するために、以下のサンプルデータを使用します。

重複した行を削除する

このコードは、A列の値のみに基づいて、重複する行をすべて削除します。

Sub RemoveDupsEx1()
Range("A1:C8").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

この例ではセル範囲 “A1:C8 “を明示的に定義していることに注意してください。代わりに、UsedRangeを使用することもできます。UsedRangeは、データの最後に使用された行と列を決定し、その範囲全体にRemoveDuplicatesを適用します。

Sub RemoveDups_UsedRange()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

UsedRangeは非常に便利で、明示的に範囲を定義する必要をなくしてくれます。 これらのコードを実行すると、ワークシートは次のようになります。

A列（1列目）のみを指定したため、以前は5行目にあった「りんご」の重複が削除されていることに注目してください。しかし、「数量」（2列目）は違います。 複数の列を比較しながら重複を削除するには、それらの列をArrayメソッドで指定します。

複数の列を比較して重複を削除する

Sub RemoveDups_MultColumns()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

Array関数で、1列目と2列目の両方（A列とB列）を使ってデータを比較するようにVBAに指示します。 指定する列番号は、連続している必要はありません。

Sub SimpleExample()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(3, 1), Header:=xlYes
End Sub

この例では、1列と3列が重複比較に使用されています。

以下のコード例では、3つの列すべてを使用して、重複をチェックします。

Sub SimpleExample()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub

テーブルから重複した行を削除する

RemoveDuplicatesは、Excelの表にも全く同じ方法で適用できます。ただし、構文が若干異なります。

Sub SimpleExample()
ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=Array(1, 3), _
Header:=xlYes
End Sub

これで、1列目と3列目（A列とC列）を基準にテーブル内の重複を削除します。 しかし、表のカラーフォーマットは整理されないので、表の下に色のついた空白行が残ります。

配列から重複を取り除く

配列から重複する値を削除する必要がある場合は、配列をExcelに出力してRemoveDuplicatesメソッドを使用し、配列を再インポートすることができます。 しかし、私たちは、配列から重複を削除するVBAプロシージャを書きました。

VBAを使用して行のデータから重複を削除する

RemoveDuplicatesメソッドはデータの列に対してのみ機能しますが、ちょっとした工夫をすれば、データの行を処理するVBAプロシージャを作成することができます。 ワークシート上にこのようなデータがあるとします。

B列とE列に先ほどと同じ重複がありますが、RemoveDuplicatesメソッドで削除することができません。 VBAを使って追加のワークシートを作成し、そこにデータを列方向にコピーして、重複を削除し、また行方向にコピーして戻すのが答えです。

Sub DuplicatesInRows()
    'ユーザに気づかれずに実行するために、画面更新とアラートをオフにする 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    '新しいワークシートの追加
    Sheets.Add After:=ActiveSheet
    '新しいワークシートを'CopySheet'とする
    ActiveSheet.Name = "CopySheet"
    '元のワークシートからデータをコピーする
    Sheets("DataInRows").UsedRange.Copy
    '作成された新しいシートをアクティブにする
    Sheets("CopySheet").Activate
    'データを列単位になるように転置して貼り付ける
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    '1列目と3列目の重複を削除する
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 3), Header _
        :=xlYes
    '元のワークシートのデータをクリアする
    Sheets("DataInRows").UsedRange.ClearContents
    '新しく作成されたワークシートからデータの列をコピーする
    Sheets("Copysheet").UsedRange.Copy
    '元のシートをアクティブにする
    Sheets("DataInRows").Activate
    
    '重複しないデータを転置して貼り付ける
    ActiveSheet.Range("A1").PasteSpecial Paste：=xlPasteAll, Operation：=xlNone, SkipBlanks:= _
        False, Transpose:=True
    '必要なくなったコピーシートを削除する
    Sheets("Copysheet").Delete
    '元のシートをアクティブにする
    Sheets("DataInRows").Activate
    '画面更新とアラートをオンに戻す
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

このコードは、元データが「DataInRows」というワークシートに保持されていると仮定しています。 このコードを実行すると、ワークシートは次のような表示になります。

E列の「りんご」の重複はこれで削除されました。ユーザーは余計なワークシートがぶら下がることもなく、きれいな状態に戻り、画面のちらつきや警告メッセージもなく、すべての処理がスムーズに行われたことになります。