#Excel Short and Sweet Tip #21 (Remove Unused Custom Formats) by David Hager

I am working on an application that generates a lot of custom number formats. But, they are created on the fly and do not need to exist permanently, so I searched for a way to delete the unused formats. Well, it turns out that the original developer of a procedure to do this (Leo Heuser) first published it in my EEE newsletter many years ago. It certainly works for what I need. However, if you use it, be aware that it will “flash” as the Format Cells dialog box is opened. This procedure is especially useful for users that receive an error message of “Too Many Custom Formats”.

Sub RemoveUnusedNumberFormats()

Dim strOldFormat As String

Dim strNewFormat As String

Dim aCell As Range

Dim sht As Worksheet

Dim strFormats() As String

Dim fFormatsUsed() As Boolean

Dim i As Integer

Application.ScreenUpdating = False

If ActiveWorkbook.Worksheets.Count = 0 Then

MsgBox “The active workbook doesn’t contain any worksheets.”, vbInformation

Exit Sub

End If

On Error GoTo Exit_Sub

Application.Cursor = xlWait

ReDim strFormats(1000)

ReDim fFormatsUsed(1000)

Set aCell = Range(“A1”)

aCell.Select

strOldFormat = aCell.NumberFormatLocal

aCell.NumberFormat = “General”

strFormats(0) = “General”

strNewFormat = aCell.NumberFormatLocal

i = 1

Do

‘ Dialog requires local format

SendKeys “{TAB 3}{DOWN}{ENTER}”

Application.Dialogs(xlDialogFormatNumber).Show strNewFormat

strFormats(i) = aCell.NumberFormat

strNewFormat = aCell.NumberFormatLocal

i = i + 1

Loop Until strFormats(i – 1) = strFormats(i – 2)

aCell.NumberFormatLocal = strOldFormat

ReDim Preserve strFormats(i – 2)

ReDim Preserve fFormatsUsed(i – 2)

For Each sht In ActiveWorkbook.Worksheets

For Each aCell In sht.UsedRange

For i = 0 To UBound(strFormats)

If aCell.NumberFormat = strFormats(i) Then

fFormatsUsed(i) = True

Exit For

End If

Next i

Next aCell

Next sht

‘ Suppress errors for built-in formats

On Error Resume Next

For i = 0 To UBound(strFormats)

If Not fFormatsUsed(i) Then

‘ DeleteNumberFormat requires international format

ActiveWorkbook.DeleteNumberFormat strFormats(i)

End If

Next i

Application.ScreenUpdating = True

Exit_Sub:

Set aCell = Nothing

Set sht = Nothing

Erase strFormats

Erase fFormatsUsed

Application.Cursor = xlDefault

End Sub

 

Continue watching here for the application I am making that needed this.

Advertisements

One thought on “#Excel Short and Sweet Tip #21 (Remove Unused Custom Formats) by David Hager

  1. Pingback: #Excel Super Links #61 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s