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 in Others by Kithuzzz
• 20,660 points
45 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 by narikkadan
• 37,660 points

Related Questions In Others

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
1,915 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
458 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
211 views
0 votes
1 answer

How is functional testing different from non functional testing?

Hey @Sinha, functional testing is performed using the ...READ MORE

answered Feb 18, 2019 in Others by Anusha
198 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,720 points
451 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,218 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 in Others by gaurav
• 22,040 points
69 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 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer

How many keywords are ideal for the META keywords tag

actually there is no official limit on ...READ MORE

answered Feb 25 in Others by narikkadan
• 37,660 points
145 views
0 votes
1 answer
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