太多不同的细胞格式 [英] Too Many Different Cell Formats

问题描述

1. 我有一个巨大的文件，10页从头开始重新创建，12张更新，5张加载原始数据，以及7张由宏用于报告。


2. 我最近添加了一个新的表格，并且正在运行到Excel 太多不同的单元格格式问题。

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.

我尝试了

已经阅读了一些谷歌的搜索结果，他们说我应该简化格式，但我甚至不知道我如何达到4000个不同的格式，更不用说我使用了多少，所以我可以删除一些。

I have read some of the google search results and they say I should simplify the formatting, but I don't even know how I got up to 4000 distinct cell formats, let alone how much I'm using which ones, so I can remove some.

它还会弹出一些文件运行的时间，但不是全部，直到它出现，然后它每次运行时都会发生。由于宏在做这么多工作，包括从头开始创建10张表，我不知道该怎么做。

It is also popping up some times the file is run, but not all, until such time as it comes up, and then it happens every time it is run. Since the macro is doing so much of the work, including creating the 10 sheets from scratch, I am not sure what to do.

有没有人知道我可以运行的一个宏的

Does anyone know

• 所有单元格格式以及有多少个单元格正在使用它们？


• 他们信任的程序有助于删除多余的单元格格式？

谢谢

推荐答案

单元格格式很复杂。细胞没有真正的格式。他们有一个字体（它本身有一个名字和一个大小），一个数字格式，高度，宽度，方向等。

"Cell formats" are complicated. Cells do not really have a "format". They have a font (which itself has a name and a size), a NumberFormat, Height, Width, Orientation, etc.

所以你需要定义你的意思格式。

So you need to define what you mean by "format".

下面是获取字体名称和大小的代码。您可以替换您喜欢的任何属性。

Below is code to get the Font Name and Size. You can substitute whatever attributes you like.

以下代码假定您已在工作簿中创建了名为格式的工作表。运行宏后，字体名称和大小将列在该工作表中。

The code below assumes you have created a Worksheet named "Formats" in the workbook. After you run the macro, the Font Names and sizes will be listed in that worksheet.

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

这篇关于太多不同的细胞格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！