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
• 38,020 points
498 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
• 63,720 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, 2023 in Others by narikkadan
• 63,720 points
400 views
0 votes
1 answer

Split the column cell value of location in 3 different columns

If the Location column has always the same pattern, ...READ MORE

answered Apr 9, 2023 in Others by narikkadan
• 63,720 points
517 views
0 votes
1 answer

How to run Macro when press enter automatically and paste in different cell

A Worksheet Change: Keep Record of Each ...READ MORE

answered Apr 10, 2023 in Others by Kithuzzz
• 38,020 points
690 views
0 votes
1 answer

Two different fonts in one cell using excel vba

Public Sub add_degree_symbol() ActiveCell.Value = ...READ MORE

answered Apr 10, 2023 in Others by Kithuzzz
• 38,020 points
900 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
1,080 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
3,496 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
• 23,260 points
804 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,690 points
976 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
• 63,720 points
552 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
• 63,720 points
1,010 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