Office TANAKA - Excel VBA Tips[「重複の削除」の変な挙動]

先日スクーの授業で「"重複の削除"には、バグとは呼べないけど、ちょこっとだけ変な挙動があるよ」と言ったところ、その件について詳しく…という質問をいただきました。次回の授業で解説してもいいですけど、VBAに限定した話なので、こちらに書いておきます。一応、2020年11月現在の最新版Excelで確認しましたけど、間違いなく再現します。ただし、先に申し上げておきますが、これはバグというには微妙な挙動ですし、そもそも正しいマクロなら何も心配する必要はありません。実害はないと言ってもいいでしょう。

まずは「重複の削除」機能のおさらいです。次のようなデータがあったとします。

言うまでもなく、A列の名前が重複しています。この表の中にアクティブセルを置いて、[データ]タブ[データツール]グループの[重複の削除]ボタンをクリックします。

実行すると[重複の削除]ダイアログボックスが表示されます。[先頭行をデータの見出しとして利用する]チェックボックスをオンにして、「名前」列だけを選択してから[OK]ボタンをクリックします。

こんな感じで重複が削除されます。

以下、少しだけ余談です。この機能は、Excel 2007で実装されました。当時、Excel 2007の開発に(少しだけ)関わっていたので、開発当初から存在自体は知っていました。その頃、あれこれと試したのですが「この機能って、あまり使われないんじゃないかなぁ～」というのが率直な感想でした。確かに、重複したデータを行単位で一気に削除してくれるのは助かりますし、実務ではよくやる作業です。でも、上図でお分かりのように、削除されるのは常に下部のデータであり、上部を削除するような設定はありません。実務は、それほど単純ではありません。下部のデータを残したいときだってあります。それに、このように重複したデータを削除するのなら、オートフィルタを使ったり、VBAでやるなど、他にもいろいろな手があります。てゆーか、今までみんな、そうやってきました。何しろ"削除"ですからね。怖いですｗ巨大な表に対して実行したとき、その結果が本当に望む結果になっているかどうか心配です。小心者の私は夜も眠れません。なので、Microsoftが想定しているほどは、使われないんじゃないかなぁ～って思いました。

「Microsoftが想定した使い方」というのは、上記のように"表全体に対して、指定した列で重複したデータ(行)を一気に削除する"という、いわば複数列の表に対しての処理です。でも、それではない使い方なら、この機能は素晴らしい威力を発揮してくれます。それは、重複しないユニークなリストを作成するときです。複数列からなる表全体に対してではなく、特定の1列だけを対象にする使い方です。ちょっと、やってみましょう。

ここでは「名前」列のデータから、ユニークリストを作成します。まず、「名前」列全体を別のセルにコピーします。

コピー先のD列に対して「重複の削除」を実行します。

ここまでできれば、項目別の集計なども簡単です。

これは便利です！実務では、よくこうしたユニークリストが必要になりますからね。もちろん今までも、ワークシート関数を駆使したり、フィルタオプションを使ったり、なんならピボットテーブルを使うなど、いくつかの方法がありましたけど、この「重複の削除」が最も簡単です。さて、前置きが長くなりましたが、変な挙動というのは、このユニークリストの作成をVBAから行うときの話です。

まず、コピー元であるA列を、別のセルにコピーします。今回は、セルD1としましょう。

Sub Macro1()
    Range("A:A").Copy Range("D1")
End Sub

コピー先であるセルD1を含む表全体(ここでは、セル範囲D1:D10)に対して「重複の削除」を実行します。

Sub Macro1()
    Range("A:A").Copy Range("D1")
    Range("D1").CurrentRegion.RemoveDuplicates 1, xlYes
End Sub

「重複の削除」は、RemoveDuplicatesメソッドで行えます。RemoveDuplicatesメソッドは、
RemoveDuplicates(Columns, Header)
のように、2つの引数を指定します。1つめの引数Columnsには「何列目の重複を調べるか」の列位置を指定します。今回は1列しかありませんから、当然1です。2つめの引数Headerには「先頭行を見出し(タイトル行)として利用するか」を指定します。今回は、タイトル行ごとコピーしましたので、これもxlYesです。

実に簡単です。たった2行だけでユニークリストを作成できるのですから、使わない手はありません。上記は、何も問題のない、正しく動作するコードです。さあ、いよいよお待ちかね。変な挙動をご紹介します。

今回「重複の削除」を行う対象のセル範囲は

Range("D1").CurrentRegion.RemoveDuplicates 1, xlYes

のように「セルD1を含むひとかたまりのセル範囲」です。だって、A列をセルD1にコピーしたのですから当然ですよね。では、A列をコピーしたセルと、「重複の削除」を行うセル範囲が、うっかり異なっていたらどうなるでしょう。

Sub Macro1()
    Range("A:A").Copy Range("F1")
    Range("D1").CurrentRegion.RemoveDuplicates 1, xlYes
End Sub

コピーした直後は、こうなります。

つまり、「重複の削除」を実行しろと指定したセル範囲内に、何もデータがなく、「重複の削除」が実行できない状態だったら、ということです。当たり前ですが、このマクロはエラーになります。

これは、手動操作で行ったときも同じです。実行することはできません。

大事なことなので、もう一度書きます。セルD1を含むひとかたまりのセル範囲に対して「重複の削除」を実行しようとしたが、そこにはデータがなかった。だからエラーになったということです。さて、上に掲示した画像には、アクティブセルが表示されていますね。アクティブセルが急に出てきました。そうなんです、今回の変な挙動は、このときアクティブセルが、どこにあるかが問題なんです。では試しに、アクティブセルをセルA1に置いて、上記のマクロを試してみましょう。実行すると、マクロはエラーにならず、下図のようになります。

はぁ？って感じです。繰り返しますが、エラーにはなりません。これ、何が起こったかというと。

セルD1を含むひとかたまりのセル範囲に対して「重複の削除」を実行しようとした
でも、そこには何もデータがなかった
本来ならそこでエラーになるはずだが、エラーにはならない
代わりに、アクティブセルを含むひとかたまりのセル範囲に対して「重複の削除」を実行した

ということです。

上記で、エラーになったケースを思い出してください。

このときは、次のような流れです。

セルD1を含むひとかたまりのセル範囲に対して「重複の削除」を実行しようとした
でも、そこには何もデータがなかった
本来ならそこでエラーになるはずだが、エラーにはならない
代わりに、アクティブセルを含むひとかたまりのセル範囲に対して「重複の削除」を実行しようとした
このときのアクティブセルは、セルD1
アクティブセルであるセルD1を含むひとかたまりのセル範囲に対して「重複の削除」を実行しようとした
でも、そこには何もデータがないのでエラーになった

これは、どう考えても変な挙動ですね。3.でエラーにならなければおかしいです。ちなみに、今回のように"アクティブセルの位置によって挙動が異なる"といえば、似たような不具合があります。

VBAでテーブルの操作「特定のデータだけコピーする」

上記ページの最後に書いていますが、テーブルに対してオートフィルタで絞り込み、その結果だけを別セルにコピーするとき、アクティブセルがテーブル外にあると、常にテーブル全体がコピーされてしまうという現象です。こちらも、本稿執筆時点(2020年11月)で修正されていません。上記ページに詳しく書いてありますが、今回の変な挙動も、おそらく同じ原因だと思われます。

ということで「重複の削除」をVBAから実行するときに起きる変な挙動についてご紹介してきましたが、私はこれを重大な不具合(いわゆるショーストッパー)だとは感じていません。だって、思い出してください。この現象が起きるのは、そもそも「重複の削除」を実行するセル範囲に、データが存在しなかったケースです。つまり、マクロが間違っているときです。正しく、対象のデータが入力されているセル範囲を指定すれば、何も問題はありません。確かに不思議な現象ですが、われわれが正しいコードを記述すれば済む話なのですから。本件を、"バグ"ではなく"変な挙動"と言ったのは、そういう理由です。みなさん、注意しましょうね。