Style problem - unable to delete it

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)<CELLSTYLES<CELLSTYLES<CELLSTYLES<CELLSTYLES<CELLSTYLES" p the (without quotes)<>

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 <CELLSTYLES and cellstyles>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! :LOL:
 
Upvote 0
Actually, I need to revise the solution I provided:

HTML:
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top