Forum Discussion
Format Painter trouble.
I have a number of rather large Excel files (100+ Worksheets with large banks of hidden cells and extensive individual cell-formatting).
For a while I could paste in the formatting required for the Worksheets, but now the program simply locks up each time I attempt to update a Worksheet format.
Is there a switch in the properties that needs to be toggled or perhaps a bin that needs cleaned out?
I am a writer using an HP ProBook 450 G4 Silver Laptop with Windows 10 and a 1tera HDD, mainly text documents (Word, Excel, Notepad).
Any assistance would be greatly appreciated.
Here is a copy of the workbook with those text boxes removed from every sheet.
I ran the doc inspector to remove my personal info from the file. In the process of doing this Excel took very long (about 5 minutes) to detect Hidden Rows and Columns.
This isn't an issue for me on my setup but it might be worth unhiding rows and columns to see how the workbook performs if you still have issues.
Doc inspector stats for hidden rows/columns:
10 Replies
1. Press alt+F11 to open Visual Basic editor
2. Go to View and make sure Immediate window is displayed at the bottom
3. Enter the following the immediate window then press enter:
? activeworkbook.Styles.Count
This will tell you how many cell styles are in your workbook. A blank workbook starts with 47 built-in. If you're in the 10s of thousands that's likely the causes of the issue.
- If these replies come up as multiple, please delete and update your site. I have replied a few times already to Patrick over the past hour and they are not appearing in the response list.Thanks for the message, Patrick.The reply is 47:? activeworkbook.Styles.Count
47The particular Workbook I'm looking at has only 37 Worksheets, truncated by Hiding cells to display and utilize only Columns A thu J and Rows 1 thru 368. This is a calendar for Gregorian, Islamic and Hebrew dates aligning through the coming centuries. A lot of data, but something I was sure Excel could handle with ease.Are you able to upload the workbook so I may have a look? You could even dummy out the data if it's confidential. The formatting is what I'm most interested in. Also, it could be the amount of used rows in a given worksheet. Go through the sheets and press ctrl+end. If it jumps down well past your data then there's a lot of rows in use.
