Too Many Different Cell Formats

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

 

Oct 28, 2022 in Others by Kithuzzz
• 27,740 points
65 views

1 answer to this question.

0 votes

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
answered Oct 28, 2022 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19 in Others by narikkadan
• 51,240 points
32 views
0 votes
1 answer

Different types of Collation Sensitivity

Following are the different types of collation ...READ MORE

answered Oct 10, 2018 in Others by CodingByHeart77
• 3,740 points
2,158 views
+1 vote
2 answers

What is the different between xml and json?

These are the differences:   It is JavaScript Object ...READ MORE

answered Feb 7, 2019 in Others by Junaid
513 views
0 votes
1 answer

How is regression testing different from functional testing?

Functional testing is testing a section of code ...READ MORE

answered Feb 18, 2019 in Others by Mythlli
226 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,385 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
277 views
0 votes
1 answer

How many is too many columns (safely) in Excel?

I'll just share a story with you. ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 51,240 points
61 views
0 votes
1 answer

How to subtract dates in Excel with different date formats?

You can use dates in any computation ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 51,240 points
50 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP