セルの書式数について

質問

Excel2002で、複数ブックからシートをコピーしてきて、ひとつのプックまとめているのですが、途中で「セルの書式が多すぎるため、書式を追加できません。」というメッセージが出てシートをコピーできなくなりました。
調べてみると、セルの書式の組み合わせの上限が約４０００であるということがわかりました。

しかし、どのシートがどのくらいのセル書式を消費しているのか不明なため、適切な対応ができません。あるひとつのブックが現在どのくらいのセル書式を使用しているのか知る方法はありませんでしょうか。
==>（理由）特定のひとつのブックだけ極端に多くの書式が使用されているとすると、そのブック（コピー元）のみをチューニングすればよいと思うため。つまりどのブックをチューニングすれば良いかを知りたいのです。
よろしくお願いいたします。

回答No.3

noname#52504 · ベストアンサー

●書式の組み合わせの数を(大雑把に)調べる

以下【書式の組み合わせ】を仮に「パターン」と表記します。

現在のパターン数を直接カウントするのはかなり大変ですが、
パターン数を意図的に増やしていくのは簡単なので、
「パターンをあと幾つ追加できるか」を調べれば、
おおよその数字を出すことができます。

例えば…

１．対象シートを新規ブックにコピーする
２．新規ブックに新規シートを追加する
　以下、新規シートについて、
３．全セルの[表示形式]をユーザー定義して、例えば[mm/dd"honya"]にする
　※追加するパターンが既存のパターンと重複しないようにするため
４．１～25列をそれぞれ異なる色で塗り潰す
５．１～25列を26～50列にコピーする
６．26～50列のフォントを太字にする
７．１～50列を51～100列にコピーする
８．51～100列のフォントを斜体にする
　※この時点でパターン数は約100。
９．１～40行のフォント色をそれぞれ異なる色に設定していく
　※１行設定していくごとにパターン数が約100増える

この作業で追加する書式の数は最大で1+25+1+1+40＝68ですが、
その組み合わせは、概算で25*2*2*(1+40)＝4100になります。

もし、23行目までは設定でき、24行目でエラーが出たとすると、
100＋100*23で約2400パターン追加できたことになり、
元あったパターン数はおおよそ1600未満ということになります。

まっさらのシートについて調べると（環境にもよりますが）
39行目でエラーが出ますから、追加数100+100*38=3900で、
元あったパターン数は100未満という結果を得られます。

ただし、複数のブック・シートについて、
その都度手作業で調べるのは、それはそれで大変ですから、
いっそマクロで…。

動作の概要
　アクティブなシートを新規ブックにコピーし、
　新規ブックにパターンを追加していく。
　パターンを追加できなくなった時点で、
　追加できたパターンの数から元の書式の数を逆算して表示する。

'------------------↓　ココカラ　↓------------------
Sub Sample()

　ActiveSheet.Copy

　On Error GoTo ErrorHandler
　Application.DisplayAlerts = False
　With ActiveWorkbook.Sheets.Add
　　With .Range(.Cells(1, 1), .Cells(50, 100))
　　　f = 1
　　　.NumberFormatLocal = """honya"""
　　　For i = 1 To 50
　　　　.Columns(i).Interior.ColorIndex = i
　　　　.Columns(i + 50).Interior.ColorIndex = i
　　　Next i
　　　With .Range(.Cells(1, 51), .Cells(50, 100))
　　　　.Locked = Not .Locked
　　　End With
　　　f = 2
　　　For i = 1 To 50
　　　　.Rows(i).Font.ColorIndex = i
　　　　If f = 3 Then Exit For
　　　Next i
　　　For j = 1 To 100
　　　　.Cells(i, j).Font.ColorIndex = i
　　　Next j
　　End With
　End With
　Application.DisplayAlerts = True
　Exit Sub
ErrorHandler:
　Select Case f
　Case 1
　　c = i * 2 - 1
　Case 2
　　f = 3
　　Resume Next
　Case 3
　　c = i * 100 + j + 1
　End Select
　MsgBox "約" & c & "パターン追加できました。" & vbCr & vbCr & _
　　"現在" & 4036 - c & "パターンぐらいです、たぶん。"
　Application.DisplayAlerts = True
End Sub
'------------------↑　ココマデ　↑------------------
Excel2000,2003で動作確認済。

※書式＞スタイルにあるスタイル数も制限の対象なので、
　まっさらのシートについて調べた場合も、0ではなく、
　既定のスタイルの数が表示されます（普通は６くらい）
※１単位で表示しますが、細かい挙動については詰めていないので、
　あくまで目安程度にお考えください。
※２行目のActiveSheet.Copyを削れば、
　アクティブなブック全体について調べます。

以上ご参考まで。長乱文陳謝。

お礼

ありがとうございます!
そうそう、これがまさに私が知りたかったことなんです。
早速作成いただいたマクロを実行してみました。
セル書式をたくさん使っているシートがわかりました。
今後、このマクロいろいろ活用させていただきます。
本当にありがとうございました。

回答No.2

Wendy02

こんにちは。

そのセルの書式の消費量の計算はものすごくややこしく、それに単なる理論値でしかありませんから、実際のところは出てこなかったと思います。

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

本来は、このようなメッセージは出ないはずなので、それが出るというのは、どこかに設定のミスが存在していることが多いです。

例：
・設定する必要のないセルには書式設定をしない。
・書式のユーザー定義で使っていないものは削除する。
・似ているものは統一する
・罫線や網掛けやパターンなどの設定を多く使わない。

お礼

やはり消費量の算出は難しいそうですね。大中小が５段階くらいでわかればと思ったのですが。ありがとうございました。

回答No.1

chie65536

コピー元をどうこうしても無意味です。

コピー先で書式の上限に近付いてるんだから、コピー前に「コピー先の、一つのブックにまとめてるブックで、全てのシートについて書式を減らして」と言う作業をしてから、コピーを行う必要があります。

でも、コピー先のブックの書式を減らしたところで、いつかは限界が来ますから、本当の解決方法は「コピーするたびに、コピー先のシートの書式を全て削除する」しかないです。

お礼

返答ありがとうございました。