【Excel VBA】オートフィルタで抽出した行だけを削除する方法ではまった話

はまったこと

• CurrentRegionプロパティは、現場の汚いExcelを相手にすると厄介な挙動を見せることがある
• 汚いExcelを扱う可能性がある場合には、CurrentRegionプロパティを過信せず、削除範囲を明示的に指定して、可視行のみ削除するようにしようという教訓

概要

• 仕事柄、一つのExcelファイルで台帳情報を作成し、その台帳情報を特定のお客様へ配布するため、ファイルを分割しなければならないことがままある

• こんなイメージのExcelファイルを会社毎に別々のExcelファイルに分割してお客様に送付する等

• 今はExcel VBAのマクロツールを作成し、このファイル分割作業を自動化しているが、職場でツールの評判がよいこともあり、調子に乗って機能追加したりしている

• そんな機能追加作業ではまったので以下教訓として供養

CurrentRegionは意外と思ったように動かない

• 今回の機能追加では、オートフィルタで抽出した行だけを削除するという機能を実装した
• 先ほどの台帳情報のイメージで会社名＝マリナーズ社以外の行をフィルタし削除するイメージ
• Google先生に聞くと、偉大な先人たちは大体以下のようなコードを使っていると教えてくれる

  '行数を1つだけ減らして見出しを範囲からはずして行削除 With Range("A1").CurrentRegion 'すべてのセル範囲 .Resize(.Rows.Count - 1).Offset(1, 0).EntireRow.Delete End With

  'オートフィルタを解除 Range("A1").AutoFilter

www.gou-blog.com

• ただこのコード、このようにヘッダーが２行にわかれているような汚いExcelを相手にすると、しっかり１行目まで範囲選択してしまい、ヘッダー行が一部消えてしまうことがある

• 結局はツールで別に保持しているデータ開始行番号の情報を活用してデータ開始行を明示的に指定し、削除処理を実装することで解決

• 今回だけに限れば、Offsetの値を (2, 0) にすれば済んだ話ではあるが、当該ツールはどのようなExcelファイルでも処理できるよう汎用的に作っており、それは美しくない
• いろいろやりようはあったと思うものの、ちょっとはまったのでCurrentRegionプロパティは注意という自戒の念を込めて