Help us understand the problem. What is going on with this article?

Excelファイル内に勝手に増殖したセルの書式をまとめて削除する方法

More than 3 years have passed since last update.

「セルの書式が多すぎるため、書式を追加できません」

Excelの異なるバージョン間で交互に編集したり、Excelファイル間でセルのコピーを繰り返していると、「セルの書式が多すぎるため、書式を追加できません」とメッセージボックスが出て編集できなくなります。ツールバーのホーム:スタイル欄 で一覧を確認すると、下図のように大量のユーザ設定が登録されています。
excel-cellstyle-big-junk.png

身に覚えのない「スタイルのユーザ設定」

なるほど、確かに多すぎる。でも、こんなユーザ設定を登録した覚えはありません!
よく見ると "20% Accent1 1", "20% Accent1 2", "20% Accent1 3" ... といった具合に、元のスタイル名の末尾に数字が付いた別名が延々と並んでいます。これは、Excelファイル間でセルをコピーする際に、コピー元ファイルが持つスタイル設定を自動的にインポートしているためと推測できます。同じスタイル名かつ同じ設定内容ならばマージしているようですが、なにかの加減でマージに失敗すると別名で登録されるため、ユーザ設定がどんどん増殖してゆきます。

この問題を解決するには、スタイルの登録を減らすしかありません。
しかしならがら、まとめて削除する手段がなく、ちまちまと一個づつ削除する不毛な作業を強いられます。
excel-cellstyle-each-delete.png

https://support.microsoft.com/ja-jp/kb/213904 によれば、「セルの書式が多すぎる」エラーが出るのは、Excel2003なら登録数が4000個、Excel2007以後なら登録数が64000個を超えた場合のようです。となると完全に綺麗にするには何千何万回も削除操作しなければなりません。そんな膨大な手作業は無理!

デフォルトのスタイル以外を全部削ってしまうマクロ

そこでマクロを使って、デフォルト(ビルトイン)のスタイルのみ残し、他を全部削ってしまうことにします。
自分で作成して登録したスタイル(とそのスタイルを設定したセルの書式)もばっさり削除されますが、後から再設定すれば良いと割り切りました。

先人を求めてネット検索すると、マクロが見つかりました。
https://gist.github.com/YoshihitoAso/311b0a1d40174e1bfdae

複数のExcelファイルを一括処理するvbs

見つけたマクロのgistをforkして、複数のExcelファイルを一括処理できるように改造してみました。

  • 下記コードを拡張子vbsをつけてファイル保存し、そこへExcelファイルをドラッグドロップすれば、Excelが起動して削除を始めます。
  • 登録スタイル数によりますが、1ファイルにつき10分以上かかるので時間に余裕があるときに実行しましょう。事前のバックアップも忘れずに。
  • 実行前にExcelを起動してウィンドウサイズを小さくし、ツールバーを畳んでおくと多少早くなる気がします。
  • コード中のN.Deleteのコメントアウトを外せば名前定義も同時に削除できます。
  • Windows7/8/10 + Excel2010 にて動作確認しました。
excel-clear-junk-style.vbs
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

For Each strFname In WScript.Arguments
    Set objDoc = objExcel.Workbooks.Open(strFname)
    ' clear all names (comment out)
    For Each N In objDoc.Names
''      N.Delete
    Next
    ' clear all user style
    For Each S In objDoc.Styles
        If Not S.BuiltIn Then
            S.Delete
        End If
    Next
    objDoc.Save
    objDoc.Close
    Set objDoc = Nothing
Next

objExcel.Quit

削除が終わり、デフォルト設定のみスタイル欄となりました。スッキリして気持ち良いです。
excel-cellstyle-clean.png

hkuno
ソフトウェア設計開発者。C/C++をメインにC#, Perl, JavaScript,lua,golangを少々。 最近は、Raspberry Pi で面白いモノを作りたいと思っています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした