Format Converting Macro

Posted on 2009-12-21
Last Modified: 2012-05-08
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??

Question by:MarshallElm
    LVL 20

    Expert Comment


    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


    Author Comment

    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??

    LVL 20

    Expert Comment


    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

    LVL 1

    Expert Comment

    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.
    LVL 80

    Accepted Solution

    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 "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.).

    There are a couple of commercial packages to deal with the problem.
    xlsgenreduc  (trial version available)     $39.95

    Excellent discussion of the problem, plus a relatively expensive solution:   Among other things, this tool lists your formats in order of usage frequency.

    Macro by Leo Heuser that removes unused cell formats:
    Because this macro has some run-on lines, here is the corrected code:

    Sub DeleteUnusedCustomNumberFormats()
    ', 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



    Author Comment

    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!

    LVL 80

    Expert Comment

    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.


    Expert Comment

    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)

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now