[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

Format Converting Macro

We have a workbook that has grown too large and has gotten to the point where we keep getting the "too many cell formats" message. I need a macro that will wipe out all of the formating so we can go and re-key it and uniform as much as possible where we can.

Does this exist??

  • 3
  • 2
  • 2
  • +2
1 Solution
Ardhendu SarangiSr. Project ManagerCommented:

if you use the following code you should be able to clear out the formatting of the entire sheet.

- Ardhendu
sub clearformats()


End sub

Open in new window

MarshallElmAuthor Commented:
Okay, that worked, but is there a way to format like cells throughout a workbook so I don't keep getting the "too many cell format" message? I want the formating...and I have over 80+ sheets that I'd have to go back and re-format with this macro. Is there something that can standarize the formats throughout the workbook so I can continue working and formatting accordingly??

Ardhendu SarangiSr. Project ManagerCommented:

Can you try the following macro?

This macro will start going thru all the 80+ tabs that you have and set the font to Arial, Size 10.

Let me know if this works for you,


p.s: Always take a backup before running any macros.
Option Explicit
Sub clearformats()
Dim i As Integer
For i = 1 To Worksheets.Count
    Worksheets(i).Cells.Font.Name = "Arial"
    Worksheets(i).Cells.Font.Size = 10

End Sub

Open in new window

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

I have worked extensively with Excel, and anyone that has knows that Excel begins to "Act Up" when a workbook grows too large or complex.  I would say that you are well beyond that point.  I would strongly reccommed that you port this out to a database (Access would work).

as far as formatting, there are NUMEROUS ways to automate it. noone could tell you the best ways without working on the workbook itself.

various techniques include looping through the worksheets object and settting the Column and/or row formats accordingly. If you need individual cells formatted a certain way, set the range object to that and format it.
Hello Elm,
Styles are one of the most underutilized features in Excel. If you format your workbook using styles rather than individually tweaking each format attribute on an instance by instance basis, then you will automatically use many fewer formats. You will also find it very easy to change the formatting for all the cells that use a given style--just edit the style parameters. In Excel 2003, you can create new styles and edit them using the Format...Styles menu item. I like to display the Style selector (found on the Format toolbar) so I can choose the desired style.

Microsoft Excel MVP Jan Karel Pieterse has a nice article on using styles at http://msdn.microsoft.com/en-us/library/dd537556.aspx "Using Styles to Dress Up Your Worksheets in Excel 2007." Despite the title, it covers Excel 2003 as well.

The exact number of different format combinations is not a hard and fast limit (normally quoted as 4000 in Excel 2003 and earlier; 64000 in Excel 2007), but once you hit it, there is no easy way to get around the problem. The best advice is to immediately close the file without saving once you hit the limit. Then reopen the older version, and try to consolidate as much as possible (formatting large blocks of cells alike, eliminating unused Custom formats, etc.). http://support.microsoft.com/?kbid=213904

There are a couple of commercial packages to deal with the problem.
xlsgenreduc  (trial version available)     $39.95    http://xlsgenreduction.arstdesign.com/

Excellent discussion of the problem, plus a relatively expensive solution: http://www.total.rowing.org.uk/quarrell/QAid/   Among other things, this tool lists your formats in order of usage frequency.

Macro by Leo Heuser that removes unused cell formats: http://groups.google.com/group/microsoft.public.excel.programming/msg/7d9f7cdf3a4b3e04
Because this macro has some run-on lines, here is the corrected code:

Sub DeleteUnusedCustomNumberFormats()
'leo.heu...@get2net.dk, May 6. 2001
'Version 1.01
    Dim Buffer As Object
    Dim Sh As Object
    Dim SaveFormat As Variant
    Dim fFormat As Variant
    Dim nFormat() As Variant
    Dim xFormat As Long
    Dim Counter As Long
    Dim Counter1 As Long
    Dim Counter2 As Long
    Dim StartRow As Long
    Dim EndRow As Long
    Dim pPresent As Boolean
    Dim NumberOfFormats As Long
    Dim Answer
    Dim Cell As Object
    Dim DataStart As Long
    Dim DataEnd As Long
    Dim AnswerText As String
    Dim ActWorkbookName As String
    Dim BufferWorkbookName As String

    NumberOfFormats = 1000
    StartRow = 3 ' Do not alter this value
    EndRow = ActiveSheet.Rows.Count

ReDim nFormat(0 To NumberOfFormats)

    AnswerText = "Do you want to delete unused custom formats from the workbook?"
    AnswerText = AnswerText & Chr(10) & "To get a list of used and unused formats only, choose No."
    Answer = MsgBox(AnswerText, 259)
    If Answer = vbCancel Then GoTo Finito

    On Error GoTo Finito
    ActWorkbookName = ActiveWorkbook.Name
    BufferWorkbookName = ActiveWorkbook.Name

    Set Buffer = Workbooks(BufferWorkbookName).ActiveSheet.Range("A3")
    nFormat(0) = Buffer.NumberFormatLocal
    Buffer.NumberFormat = "@"
    Buffer.Value = nFormat(0)


    Counter = 1
        SaveFormat = Buffer.Value
        SendKeys "{TAB 3}"
        For Counter1 = 1 To Counter
            SendKeys "{DOWN}"
        Next Counter1
        SendKeys "+{TAB}{HOME}'{HOME}+{END}^C{TAB 4}{ENTER}"
        Application.Dialogs(xlDialogFormatNumber).Show nFormat(0)
        ActiveSheet.Paste Destination:=Buffer
        Buffer.Value = Mid(Buffer.Value, 2)
        nFormat(Counter) = Buffer.Value
        Counter = Counter + 1
    Loop Until nFormat(Counter - 1) = SaveFormat

ReDim Preserve nFormat(0 To Counter - 2)


    Range("A1").Value = "Custom formats"
    Range("B1").Value = "Formats used in workbook"
    Range("C1").Value = "Formats not used"
    Range("A1:C1").Font.Bold = True

    For Counter = 0 To UBound(nFormat)
        Cells(StartRow, 1).Offset(Counter, 0).NumberFormatLocal = nFormat(Counter)
        Cells(StartRow, 1).Offset(Counter, 0).Value = nFormat(Counter)
    Next Counter

    Counter = 0
    For Each Sh In Workbooks(ActWorkbookName).Worksheets
        For Each Cell In Sh.UsedRange.Cells
            fFormat = Cell.NumberFormatLocal
            If Application.WorksheetFunction.CountIf(Range(Cells(StartRow, 2), Cells(EndRow, 2)), fFormat) = 0 Then
                Cells(StartRow, 2).Offset(Counter, 0).NumberFormatLocal = fFormat
                Cells(StartRow, 2).Offset(Counter, 0).Value = fFormat
                Counter = Counter + 1
            End If
        Next Cell
    Next Sh

    xFormat = Range(Cells(StartRow, 2), Cells(EndRow, 2)).Find("").Row - 2
    Counter2 = 0
    For Counter = 0 To UBound(nFormat)
        pPresent = False
        For Counter1 = 1 To xFormat
            If nFormat(Counter) = Cells(StartRow, 2).Offset(Counter1, 0).NumberFormatLocal Then
                pPresent = True
            End If
        Next Counter1
        If pPresent = False Then
            Cells(StartRow, 3).Offset(Counter2, 0).NumberFormatLocal = nFormat(Counter)
            Cells(StartRow, 3).Offset(Counter2, 0).Value = nFormat(Counter)
            Counter2 = Counter2 + 1
        End If
    Next Counter
    With ActiveSheet.Columns("A:C")
        .HorizontalAlignment = xlLeft
    End With
    If Answer = vbYes Then
        DataStart = Range(Cells(1, 3), Cells(EndRow, 3)).Find("").Row + 1
        DataEnd = Cells(DataStart, 3).Resize(EndRow, 1).Find("").Row - 1
        On Error Resume Next
        For Each Cell In Range(Cells(DataStart, 3), Cells(DataEnd, 3)).Cells
            Workbooks(ActWorkbookName).DeleteNumberFormat (Cell.NumberFormat)
        Next Cell
    End If
    Set Cell = Nothing
    Set Sh = Nothing
    Set Buffer = Nothing
End Sub


MarshallElmAuthor Commented:
Hmm...What if this went in a different direction. Is there a macro compatible with Excel 2000 that would deactive formats on worksheets that will not be used if a specific user interface button is pressed? For instance, our customer will press "x" if they want a full blown budget increase and "y" if they want to purse the Fast Track... To make the Fast Track tabs work, can I deactivate the rest of the workbook based on the decision made at the point of entry??

Wrap your heads around that one!

You can't deactivate a format, but you can delete the worksheet that uses it. Once that has been done, you can run Leo Heuser's macro above to remove unused Custom number formats.

That said, I fail to see the advantage. If you are close to the limit for cell formats, the full blown budget increase workbook will always have that problem. The approach you describe won't help that workbook in the slightest.

Very true. Hence my previous comment of upgrading the document to a database.  If you are reaching these type of limitations, then chances are, your are reaching the limits of the workbook all together. If you have MS Access, then you can very easily convert it. (Well easy depending on your skill levels)
MarshallElmAuthor Commented:
This was the most informative answer received. While I did not end up using any of the formula's created I felt this to be the most useful in my education on the matter.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now