VBAで行を一括削除出来ない現象と対策について

VBAで行を一括削除するコードにおいて、思わぬ落とし穴が見つかったので対策を説明する。

---

落とし穴とは・・・

シートの準備

まずはこんなデータを用意する。

そして、B列のフィルタで「いいい」を非表示にする。

フィルタ結果はこうなる。

※もし実際に試す人はこのシートを複数コピーしておくと幸せになれるかもしれない。

「A列が空白の行を削除する」コード

Alt+F11でVBEを開いて、適当なモジュールに「A列が空白の行を削除する」コードをコピペする。

今回はこの記事を書く発端となった下記の記事のコードを拝借する。

infoment.hatenablog.com

Sub RowDelete()
    Range("A2:A10").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

実行結果

そして実行結果はこうだ。

正常に動いているように見えるが・・・？一応、フィルタを解除してみよう。

消えてねぇええええええええええくぁｗせｄｒｆｔｇｙふじこｌｐ＠

原因の追求

1. データが悪いんじゃね？

気を取り直して、まずはデータに問題があったかもしれないので、フィルタを解除した状態でもう一度実行してみる。

データには問題なかった(・8・)

2. SpecialCellsが悪いんじゃね？

事前にコピーしておいたシートを使って、SpecialCellsの挙動をチェックしてみよう。

Sub RowDelete_Check()
    Debug.Print Range("A1:A10").SpecialCells(xlCellTypeBlanks).Address
End Sub

$A$4:$A$5,$A$9

全く問題なかった。

SpecialCells様、あなた様を疑った私めをどうかお許しください。

3. まさかDeleteメソッドに問題が・・・？

再びコピーしておいたシートを使って、下記のコードを実行してみる。これで消えないわけ無いだろう！

Sub ずばっとRowDelete()
    Range("4:4,5:5,9:9").Delete
End Sub

本当に消えなかった。

えぇい、ままよ。

Sub ちまちまRowDelete()
    Range("9:9").Delete '←並び順に注意
    Range("5:5").Delete
    Range("4:4").Delete
End Sub

よっしゃ、倒したぜ！！！

でもこれだと高速化出来なくね・・・？

そうです。この方法だと先の記事で言う一番最初の、最後の行から順にDeleteしていく方法になってしまいます。

しかし、1行づつ消す処理はとてつもなく低速で、実用できるレベルではありません。

これではSpecialCellsを使った方法も、Unionで固めてから消す方法も、どっちも対応出来ないことになります。

解決策を考えてみよう

1. 再表示してから消せばいいよね。

要は非表示だからダメなんです。

ストレートに行を表示してから消すようにしよう

Sub RowShowDelete()
    With Range("A1:A10").SpecialCells(xlCellTypeBlanks).EntireRow
        .Hidden = False
        .Delete
    End With
End Sub

消えてねぇええええええええええくぁｗせｄｒｆｔｇｙふじこｌｐ＠（再び）

長くなるので省略するが、上記をF8（ステップ実行）したところ、.Hidden = Falseが無視されていることが分かった。

2. 原因の判明

そこで、ようやく気がつく。

これオートフィルタが犯人や

オートフィルタではなく、単なる「行の非表示」であれば当初のコードでも正常に一括消去出来ることを確認した。

が、そんな場面は稀だろう。私はオートフィルタに対応させたいのだ！

3. 謎のひらめき

そんな中で、天からアイディアが降りてきた。

Hiddenプロパティを逆転させてみよう。

Sub RowUnShowDelete()
    With Range("A1:A10").SpecialCells(xlCellTypeBlanks).EntireRow
        .Hidden = True
        .Delete
    End With
End Sub

何故かうまくいった。

オートフィルタが適用されていない場合や、単なる「行の非表示」がされている時も問題なし！

めでたし。めでたし。

まとめ

正攻法だと

1. オートフィルタのフィルタ情報を取得
2. フィルタを全て解除
3. 行を一括削除
4. フィルタを再適用

という流れになるはずだったので、シンプルな解決法が見つかったのは本当に良かった。

行の一括削除をする時に元データがフィルタされている可能性がある場合は、ひと工夫必要なことを知っておくべきだろう。

また、元データが「テーブル」で定義されている場合は、複数行を一括で削除出来ないという（EXCELの）制約があるので、この方法でも対処することが出来ないことも頭の片隅にでも置いておくとよいかもしれない。

以上

---

もし情報をお持ちの方はコメント欄でもTwitterでも良いので、教えてくれると助かります。

それでは、また今度♪　ちゅんちゅん(・8・)