Solved: Stubborn Styles (How To Remove Them?)

[doctorp]

Hi All

This is a tricky one. I have a file which has accumulated a number of styles in it (200+). I have managed to remove them all, but there are some remaining which I can't fgure out how to remove. Any advice?

File Attached.

[doctorp]

So obviously my wording is a little off. I have removed most of them, not all.

[geekgirlau]

They're stubborn alright

I tried looping through the styles and deleting all but a couple of specific styles (such as Normal) - most gave me no error messages, but it didn't delete any of the styles either!

I don't know what to suggest here, but I can't think of another way to remove them - sorry!

[lucas]

This might have something to do with it:
[vba]Sub a()
MsgBox ActiveWorkbook.Styles.Count & " Styles"
MsgBox ActiveWorkbook.Names.Count & " Defined Named Ranges"
End Sub[/vba]
I get 29 Styles and 51 Named Ranges.
I can remove the named ranges with this from one of Jakes kb entries:
[vba]
Sub RemNamedRanges()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
nm.Delete
Next
End Sub
[/vba]
But I can't get this to work on Styles:
[vba]Sub RemStyle()
Dim st As Style
For Each st In ActiveWorkbook.Styles
st.Delete
Next
End Sub[/vba]
I think you might be better off to copy the sheets to a new workbook..

[geekgirlau]

I think you'll find if you copy the sheet, the style goes along for the ride ...

[lucas]

I hadn't tried it but it appears that you are correct Anne...? I never use styles so I'm kinda at a loss..

[geekgirlau]

Untested, but maybe you could copy the cells and do a paste formula. You can't paste formats as this copies the style. I guess it depends on how badly you want to get rid of the styles versus how much time it will take to reformat the workbook ...

[doctorp]

Thanks for all your help so far. Reformatting the workbook would take days so I'd rather leave the stubborn styles behind than reformat the workbook. Its almost as if this is a bug in excel.

[Ivan F Moala]

After removing named ranges ....
Try saving as Html file and then back again


see atttached

[Ivan F Moala]

Sorry ... just in case .... do this on a Back up

[lucas]

Saving it as html and back did work....I would never have thought to do that.

[doctorp]

May the excel Gods bless you. A great solution which preserves all existing used styles, but also copies the normal template of styles back into the workbook. I just hope no serious functionality is lost through saving to HTML and back again. Thanks again.