Style problem - unable to delete it

[klb]

I have a style in a large and complicated workbook (over 20 sheets) that I want to delete. Unfortunately it once was a Normal style in another workbook and I have been unable to delete it. I also have a "regular" Normal style that cannot be deleted.

I am trying to clean up this workbook for distribution to others in our organization. There are sheets within the workbook that will be made a part of other workbooks. Getting rid of this unwanted style is not critical it is just one of those things that bug me. (we have all been there)

Any help would be greatly appreciated.

[Mark W.]

There's an ASAP Utilities | Format | Remove all unused styles... menu command installed by the free ASAP Utilities add-in. See www.asap-utilities.com.

[ This Message was edited by: Mark W. on 2002-04-04 14:16 ]

[Jack in the UK]

Hi

Just a change will resetting format style to normal and modify button to catergory general.. if my memeory is any good will this not remove the now set style.. ie ste to defaut...

[klb]

Mark, Thanks for the info on the ASAP Utilities. Installed them and I know I will find them useful but....even though I tried the "remove unused styles", the style is still there.

This may have worked IF I could find where this style is being used. Have not been able to figure out a way to search for a style.

Like I said Excel is protecting this style the same way it does the "Normal" style. Did just notice an added little quirk, the problem style has a space at the end of its name. I don't know if this makes a difference or not.

Thanks

[Mark W.]

klb, what if you modified the "offending" style to display some "eye-popping" pattern. Perhaps that would help you find where it was being used.

[klb]

Changed the color and font size so that it would jump out. The style is not being used. After running the asap utility I get a message to the effect that there were 13 styles and 12 are in use. The other 18 were removed. I know, the math doesn't make sense to me either.

Saved and then closed the file. Reopened it and ran the utility again with the same results.

Net result is the style is still there.

[Mark W.]

Sounds like you might be dealing with a corrupt worksheet. What if you use Copy/Paste Special... to move the contents and formats to another workbook?

[klb]

Already tried that but.....

There are almost 600 ranges named in the original sheet and each would have to be redefined. There is considerable formatting on some of the sheets. That is why I am looking for some other way to eliminate the style.

If I copy the sheet, then the styles come with the copied sheets. And can't be deleted in the copied worksheet either.

You aren't the first person to be stumped by this problem.

[DrillRaider]

Hi everyone. This is my first time posting on this forum. I actually found a solution to this issue and want to contribute.

Here are the step-by-step process in removing all styles including the ones with invalid names:

1. Rename your Excel workbook from .xlsx or .xlsm to .zip

2. Open the workbook with WinZip and navigate to xl folder

3. Right click on styles.xml and choose Open With Notepad

4. Hit Ctrl F to search for cellstyles (with the open XML tag mark in the front, can't make it show in the post)

5. Cancel out of the find dialogue box, move your cursor before cellstyles and hit enter twice (this is to mark where the beginning of this tag is)

6. Hit Ctrl F again and search for /cellstyles>

7. Highlight everything between step 5 and step 6 (everything in the cellstyles tags including the tags) and hit delete

8. Save the Notepad and exit Notepad. WinZip should now ask you whether to update the file. Select Update Zip file with changes. Hit OK and close out WinZip.

9. Rename the workbook zip file back to .xlsx or .xlsm

The deed is done!

[DrillRaider]

Actually, I need to revise the solution I provided:

1. Rename your Excel workbook from .xlsx or .xlsm to .zip

2. Open the workbook with WinZip and navigate to xl folder

3. Right click on styles.xml and choose Open With Notepad

4. Hit Ctrl F to search for "<cellstyles"

5. Replace everything between "<cellstyles" and "/cellstyles>" with the following XML code:
  <cellStyles count="1">
    <cellStyle name="Normal" xfId="0" builtinId="0" customBuiltin="1" />
  </cellStyles>

6. Save the Notepad and exit Notepad. WinZip should now ask you whether to update the file. Select Update Zip file with changes. Hit OK and close out WinZip.

7. Rename the workbook zip file back to .xlsx or .xlsm

The reason that you need to keep this section is that if you try to save the .xlsm or .xlsx as .xlsb, and resave the .xlsb, it will give you an error.