Solved

Too many different cell formats. I get this error when i run any script and work on any thing in this excel.

Posted on 2009-05-05
10
933 Views
Last Modified: 2012-05-06
Hi,.

Too many different cell formats. I get this error when i run any script and work on any thing in this excel.

How can i find which colum or cells has these many different formatting.

I changed all to similar font or format. But still get this issue.

Regards
Sharath
0
Comment
Question by:bsharath
  • 6
  • 2
  • 2
10 Comments
 
LVL 13

Assisted Solution

by:Brian Withun
Brian Withun earned 100 total points
ID: 24307113
It sounds like you have several different cell formats.  Some of which may not even be needed, if they are formatting a cell that has no values typed into it.

I don't know of a way to COUNT or ITEMIZE the various unique formats, but here's a sure thing:

(this will remove ALL formatting from your document, but will not erase ANY of your data)

--use this with care.  Backup your sheet first.

1) [CTRL] A -- select all
2) Edit > Clear > Formats
3) Re-apply any formats that are important to you.

0
 
LVL 11

Author Comment

by:bsharath
ID: 24307137
Thanks but what all include in this format reset.

0
 
LVL 11

Author Comment

by:bsharath
ID: 24307139
Thanks but what all include in this format reset.

0
 
LVL 13

Expert Comment

by:Brian Withun
ID: 24307327
This will remove ALL formats from all cells in the active worksheet.  If you do this on every worksheet in your workbook, I can pretty much guarantee that you'll only have __1__ format left.  Your "too many different cell formats" message will go away.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24312879
Sharath,

The best tool that I have found for removing this problem from large spreadsheets (where manually removing the formatting in large chunks is not practical or desireable) is by a crowd called xlsgen, http://xlsgenreduction.arstdesign.com/index_en.html. While it does cost $50 it is a must have utility

Free tools - that from my experience don't achive the same quantity of reduction as xlsgen - but will still help include Leo Heuser's code  at http://www.j-walk.com/ss/excel/eee/eee007.txt and ASAP utilities, http://www.asap-utilities.com/

Cheers

Dave
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 11

Author Comment

by:bsharath
ID: 24313014
Dave i tried the first like. That is not free but when selected the xlsm file i get an error

The sceript can you tell me which code should i use?

ASAP is this free and will this work for the issue i have
0
 
LVL 11

Author Comment

by:bsharath
ID: 24313015
Dave i tried the first like. That is not free but when selected the xlsm file i get an error

The sceript can you tell me which code should i use?

ASAP is this free and will this work for the issue i have
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 400 total points
ID: 24313075
Sharath,
Excel 2007 (your xlsm file) has 64,000 formats ....  the xlsgen is designed to work with Excel 2003 and earlier as these versions have 4000 formats which large files sometimes breach. Is you problem file an issue in @007?
I have listed Heuser's code below
Both it and the free ASAP addin will help, but as above the xlsgen tool is custom built for this problem
Cheers
Dave

Sub DeleteUnusedCustomNumberFormats()

    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 Dummy As Variant

    Dim pPresent As Boolean

    Dim NumberOfFormats As Long

    Dim Answer

    Dim c As Object

    Dim DataStart As Long

    Dim DataEnd As Long

    Dim AnswerText As String
 

    NumberOfFormats = 1000

    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

    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)

    Worksheets(Worksheets.Count).Name = "CustomFormats"

    Worksheets("CustomFormats").Activate

    Set Buffer = Range("A2")

    Buffer.Select

    nFormat(0) = Buffer.NumberFormatLocal

    Counter = 1

    Do

        SaveFormat = Buffer.NumberFormatLocal

        Dummy = Buffer.NumberFormatLocal

        DoEvents

        SendKeys "{tab 3}{down}{enter}"

        Application.Dialogs(xlDialogFormatNumber).Show Dummy

        nFormat(Counter) = Buffer.NumberFormatLocal

        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
 

    StartRow = 3

    EndRow = 16384
 

    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 ActiveWorkbook.Worksheets

        If Sh.Name = "CustomFormats" Then Exit For

        For Each c In Sh.UsedRange.Cells

            fFormat = c.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 c

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

        .AutoFit

        .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 c In Range(Cells(DataStart, 3), Cells(DataEnd, 3)).Cells

            ActiveWorkbook.DeleteNumberFormat (c.NumberFormat)

        Next c

    End If

Finito:

    Set c = Nothing

    Set Sh = Nothing

    Set Buffer = Nothing

End Sub

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 24313096
Yes i have this issue in Excel 2007
0
 
LVL 11

Author Comment

by:bsharath
ID: 24313097
Yes i have this issue in Excel 2007
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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.

760 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

20 Experts available now in Live!

Get 1:1 Help Now