Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Deleting Styles (XL 97)

    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....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting Styles (XL 97)

    Payne Consulting Group has a style cleaner for download: http://www.payneconsulting.com/payneconsul.../exceltips6.htm

    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.

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Deleting Styles (XL 97)

    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

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting Styles (XL 97)

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

    Thank you very much!

  5. #5
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Deleting Styles (XL 97)

    Dean,

    Here you go...

    Attached is "Formats and Styles" Excel add-in.

    Regards,

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

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting Styles (XL 97)

    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!

  7. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Deleting Styles (XL 97)

    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

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting Styles (XL 97)

    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •