Delete Custom Number Formats

[lmoon]

Hi - I've done a search and couldn't see anything which seemed to solve my problem. I have a spreadsheet into which a client has copied a number of worksheets. Unfortunately, all their custom number formats have also been copied across and I continually get the error message "too many custom number formats" and it is running very slowly. I seem to remember there being a neat VBA solve for this, but I can't find it on the interweb anywhere...anyone out there have a simple (ish) fix for this???

Thanks in advance

lmoon

 

[dave3009]

Hi There

Welcome to the Board

Give this a bash

Sub clean_Format()
For Each Worksheet In ActiveWorkbook.Worksheets
    Cells.NumberFormat = "General"
Next
End Sub

Dave

 

[lmoon]

Thanks for the quick response....

tried it out, but it cleared all the formatting in my current worksheet, but hasn't deleted the custom formats from the "format cells" / "number" / "custom" which is what i was hoping to do. I'd like to get rid of any custom number formatting that is not currently being used in my worksheet.....

 

[dave3009]

Apologies

I skipped over an important point

Sub clean_Format()
Dim ws As Worksheet
For Each ws In Workbooks(ActiveWorkbook.Name).Sheets
    ws.Cells.NumberFormat = "General"
Next ws
End Sub

KR


Dave

 

[lmoon]

Thanks once again Dave.

I don't think that it's quite doing what I want, which is to delete the unused custom formats. I have heaps of "#,##0.0_)" or "#,##0.0000_)" or "0.0x" "0.000x" "0.0000x_)" which are very similar but not the same and I'd like to delete any formats which are not being used in the current worksheet so that they don't appear in the custom number box. I know you can go through and do it manually, but I'd ideally like a quick fix. (Lazy, moi?). The vba you suggest doesn't seem to delete the unused options, just returns the current worksheet to the general format....

 

[dave3009]

Hi lmoon

I apologise for not getting back on this, I have been working to try and help but up til now I have not been able to bodge you up anything that truly works.

If i were you I might look at the .DeleteNumberFormat method and see if you can do something

Sorry, I hope one of the true gurus can help.

Best Wishes


Dave

 

[dave3009]

Hi

Had a think about this, OK so I couldn't come up with a solution to removing the Custom Format types not used in the current workbook but your question does mention the formats have been copied across your book which in turn may be causing your problem.

Perhaps reseting the used range of each page might trim the file down to size, I got a book from our HQ which was 8Mb I removed the excess formatting and it (within a second) trimmed down to a meager 3Mb

>>Click Here<<

See if it gets you somewhere towards your goal

Dave

 

[Kenneth Hobson]

I guess something like the macro in this link would work. I advise caution as it may delete more than you expect. You might answer no to see what you have first. Work on a backup copy to test.
http://www.themssforum.com/ExcelMisc/determine-number-521953/