Too Many Different Cell Formats

Question

1. I have a massive file with 10 sheets recreated from scratch, 12 sheets updated, 5 sheets loaded with raw data, and 7 sheets that are used by the macros for the report.
2. I have recently added a new sheet and am running into the Excel "Too many different cell formats" problem.

Does anyone know

• of a Macro, I could run to get a listing of all the cell formats and how many cells are using them.
• of a program, they trust to help remove excess cell formats?

 

Answer 1

Cell formats are challenging. There isn't actually a "format" for cells. They include a font (which has a name and size of its own), a NumberFormat, Height, Width, Orientation, etc.

Therefore, you must clarify what you mean by "format.". The font name and size can be found in the code below. You are free to substitute whatever qualities you choose.

The code that follows presupposes that a Worksheet called "Formats" already exists in the workbook. The Font Names and sizes will be listed in that worksheet when you run the macro.

Public Sub GetFormats()

    Dim CurrentSheet As Integer
    Dim UsedRange As Range
    Dim CurrentCell As Range
    Dim rw As Long

    Sheets("Formats").Cells.ClearContents
    rw = 1
    For CurrentSheet = 1 To Sheets.Count
        Set UsedRange = Range(Sheets(CurrentSheet).Range("A1"), Sheets(CurrentSheet).Range("A1").SpecialCells(xlLastCell))
        For Each CurrentCell In UsedRange
            FontUsed = CurrentCell.Font.Name + ":" + CStr(CurrentCell.Font.Size)
            If Sheets("Formats").Cells.Find(FontUsed) Is Nothing Then
                Sheets("Formats").Cells(rw, 1).Value = FontUsed
                rw = rw + 1
            End If
        Next
    Next CurrentSheet
End Sub