Excel
ExcelVBA
効率化
マクロ
エクセル
Excel VBADay 21

特定書式のセルの値を高速で消去する方法

"このVBA処理は遅いからお昼休みに実行" に思うこと

たとえ長時間でもVBAは作業を続けてくれますよね。次のようなフレーズ聞いたことありませんか?
処理が遅いからお昼休憩前に処理を実行していこう
仕事からの帰宅前に処理を実行して朝一チェックできればいい
処理専用にPC一台を容易してフリーズしているように見えるけど半日くらいで処理は完成するぞ
単純な発想の転換でその処理は爆速化することが可能な場合もあります。今回は私が知っている一例をご紹介します。

今回使うもの

今回の主役はこの「検索と置換」の画面です。誰もが検索は良く使っていると思います。
gazou.png
このページのタイトルとは一見無関係にも見えるかもしれませんがこれが今回の主役です。

解決するテーマ:特定書式のセルの値を高速で消去する

このページを見ていらっしゃる方は共感いただけると思いますが、Excelはとても便利でデータ入力や処理を司ります。また、そのような機能を仕込んでいくと、例えば一定条件を満たすセルへの巡回処理がいつか実装の必要が出てきますよね。巡回した上で例えばコメントを付与したり、そのセルの色を変えてみたりと様々な処理を施すことと思いますが、お察しの通りとにかく巡回処理は遅いのでみなを困らせます。良いプログラムを書いたりアルゴリズムの見直しで改善する場合も多々あると思いますが、難しいプログラムを書くにはスキルが必要となりますので誰もが出来ることではありません。今回はそのような悩ましい巡回処理のスピードのうち一部切り出しですが特定の書式を持ったセルのあたりを爆速で消去する方法をご紹介します。

「消去する」のではなく「空欄への一括置換」と考える

手短に水色セルの値や数式を爆速で消去する方法を説明すると「置換」-「オプション」でターゲット書式を指定、置換前後文字列欄はブランク、置換後書式は「セットなし」。
gazou.png
たったこれだけで水色の塗りつぶしセルを対象に、セルの書式は何も変更を加えることなく、そのセルの文字や数式を削除してくれます。通常For文などを使った場合との処理速度の違いは試してみて頂ければわかりますが、比較するまでもなく爆速です。お手元のファイルでお試しください。すぐにその処理の異常な速さを感じていただけると思います。

この置換処理が良いのは次のような一般的な書式設定は全て対応が可能であり、複合的な書式も処理可能な点です。この複合的な書式も処理可能というのは例えば、セルの色が黄色であり、かつ、保護設定の項目「ロック」のチェックが外れている項目、などといった書式のことをここでは示しています。
gazou.png

ここまではExcelでの操作に関する説明ですが、VBAで表現してみますと次の通りです。
例:ロックされているセルの値や数式を消去する。(Selection.Replaceの細かい部分は検索してください)

    '検索書式のクリア
    Application.FindFormat.Clear
    '検索条件に「ロックされていないセル」を指定
    Application.FindFormat.Locked = False
    '処理の実行(Replacement:=の部分が検索後文字列なので””としておくのがポイントです!)
    Selection.Replace What:="", Replacement:="" _
        , LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
        :=True, ReplaceFormat:=False
    '検索書式のクリア
    Application.FindFormat.Clear

この処理をうまく使えば今まで悩んでいた処理速度問題が解消することもあるかもしれませんね。
ただ、今回は爆速処理は爆速処理でも「書式」ありきでのお話のため、そんな都合良く書式が統一されてはいないからこの処理は活用出来ないという方もいらっしゃると考えています。ただ、処理優先する際に本当にその今の「書式」は大事でしょうか。現在の書式を優先してFor文やWhileで回して半日や数時間、数分待つよりも、そのForやWhileでやっていた条件部分を書式に寄せることにより消去処理が高速化するのであれば私は爆速処理対象セルに一定の書式ルールを設けてそれを目印に上記処理を書けるような方針を設定することは合理的な判断と考えます。

この機能の応用

もうお気づきとは思いますが、こちらは消去処理のみならず、ある書式を他の書式に置き換えることも爆速処理できますし、ある書式のセルに対する消去ではなく文字列や数字、数式の配置も可能になります。書式という一見するとユーザにとって情報把握を助けるための原始的な情報とも捉えられがちですが非常に意味のある重要な情報の一種です。ステム処理で活用すればいくらでも応用の範囲がありそうということにこのページでお気づきいただければ嬉しいです。

最後に

今回は今あるシートに対する特定セルに関する消去処理の高速化のご紹介でしたが、そもそも「何もなかったこと」を目的とするのであればもしかしたら前回ご紹介した「ファイルを最終保存時の状態に一瞬で戻す」を活用するほうが良かったりするかもしれません。今回のご紹介も前回のご紹介も、Excelとしては決して目新しいものではなく誰もが見ていたり近くまで知っていた機能だと思います。そのような大したことなさそうなちょっとした機能や気づきを活かせば、あっと驚くような結果をもたらすことがあります。そのような発見がまだまだきっとあるだろうということに気がついていただき、今まで無理だと思っていた処理も意外と簡単な方法で解消するかもしれないと考えて難しい課題にチャレンジして見る方が増えればよいなと思います。「Excel VBA Advent Calendar 2017」に会社のメンバーで書こうということになり私はこれで2017年12月分最後の記事となります。末筆とはなりますが健康には気をつけてよいお年をお迎えください。