Deleting Styles (XL 97)

Topic

New Reply

ONe of my co-workers has a massive spreadsheet file that seems to have grown by inclusion of sheets from other files, cut-and-paste operations, etc. He has a bunch of links to other files, none of which are now required. On examination, it seems that many of them a related to styles of the type “Normal_Budget_87-Rev1” or “Currency[0]_Summary_Operations_North” etc.

Is there an easy way to delete a number of styles at a time? Going through and deleting one-at-a-time on the style format dialogue box is pretty tedious. I tried a search here on “delete” and “style” but didn’t come up with much.

Thanks in advance….

Reply | Quote

Replies

Payne Consulting Group has a style cleaner for download: http://www.payneconsulting.com/payneconsul…/exceltips6.htm%5B/url%5D

Note: in Excel 2000 and up, saving a workbook as HTML, then resaving as XLS gets rid of a lot of junk in a workbook. You may lose too much in Excel 97.

Reply | Quote

Dean,

I’ve written an Excel add-in (“Formats & Styles”) that will list all styles in a workbook or delete all unused styles in the workbook.
It has the same options for custom number formats.
An additional option is available to batch delete selected styles.
A new menu item is added to the format menu that provides access to the program.
I’ve attached the Word.doc instruction page (31kb-zipped).
If you or anybody else is interested, post back and I will attach the program (98kb zipped) in a separate post. (100 kb limit).

Regards,

Jim Cone
San Francisco, Ca
jim.coneXXX@rcn.comXXX

Reply | Quote

Jim – that would be terrific. At your convenience, could you please post a copy?

Thank you very much!

Reply | Quote

Dean,

Here you go…

Attached is “Formats and Styles” Excel add-in.

Regards,

Jim Cone
San Francisco, CA
jim.coneXXX@rcn.comXXX

Reply | Quote

Thanks, Jim – that’s terrific.

The add-in works flawlessly on my computer, but on my co-worker’s he gets error messages – “unable to set the weight property of border object” and “unable to set the patterns property of interior object” when he tries to use the ‘simplify formats’ function. Everything else seems to work properly. It is not a big deal, and we will see if it recurs on other spreadsheets – but if you want to track it down I can get the particulars of his installation – revision, O/S version (Win 2K, but I don’t know the service pack), etc

Thanks again – it is very helpful!

Reply | Quote

Dean,

Thank you for responding.

Yes, I would like to track down the source of the error you reported, but that can be very difficult via email.
Could you determine if there are any protected sheets in the problem workbook and, if so, unprotect them and try again.
Also, I would like to know the Excel version and what service packs have been applied.
Any further comments you might have would be appreciated.

The program does take protected sheets into account, but maybe something happened I didn’t consider.
For what its worth, the program has been run on NT4.0 and Windows 95 & XP with both XL97 and XL2002.

Finally, were you able to accomplish the original task of removing unwanted styles?

Regards,

Jim Cone

Reply | Quote

Jim – both my colleague and I are able to delete unused styles and formats (which is all we really wanted to do, so it is tempting to quit while we are ahead!), and I am able to use the “simplify formats” tools. I am not sure about the exact settings for the s/sheet that is giving him problems, but we will try different combinations over the next couple of days, and send you the results directly. Thanks for your help,

Reply | Quote