Delete multiple custom number formats with VBA

[paulym666]

I have spreadsheet which for some reason have a large number of custom number formats (pages and pages in the custom format pane). Some of them are so complex I can't even guess what they look like. I think this might be the cause of some bloating of file size (I've already deleted hundreds of custom styles and unused range names).

Is there some simple VBA I can use to delete all custom number formats that aren't built in to Excel? I used "stylekiller" to delete the custom styles and thought I could edit that VBA to delete custom number formats - but I can't see a way to do it.

Does anyone know a way?

 

[RoryA]

I don't think there's a simple way to do it via VBA as there isn't a collection of them exposed in the object model. It's probably simpler, assuming a current workbook format, to edit the styles.xml part of the workbook structure to just remove the numFmts section completely.

 

[footoo]

Delete Custom Formats

Jim Rech posted the below code I think more than ten years ago. It has served me well in Excel versions prior to 2007. Any ideas how this should be changed in order to to work in Excel 2007. Sub

answers.microsoft.com