终极解决EXCEL不同单元格格式太多的问题.docVIP

终极解决EXCEL“不同单元格格式太多”的问题 2009-06-24 14:35 在格式超过4000的Excel表里录制一个新宏，内容如下所示： Sub DeleteUnusedCustomNumberFormats()Dim Buffer As ObjectDim Sh As ObjectDim SaveFormat As VariantDim fFormat As VariantDim nFormat() As VariantDim xFormat As LongDim Counter As LongDim Counter1 As LongDim Counter2 As LongDim StartRow As LongDim EndRow As LongDim Dummy As VariantDim pPresent As BooleanDim NumberOfFormats As LongDim AnswerDim c As ObjectDim DataStart As LongDim DataEnd As LongDim AnswerText As StringNumberOfFormats = 1000ReDim 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 FinitoOn Error GoTo FinitoWorksheets.Add.Move after:=Worksheets(Worksheets.Count)Worksheets(Worksheets.Count).Name = CustomFormatsWorksheets(CustomFormats).ActivateSet Buffer = Range(A2)Buffer.SelectnFormat(0) = Buffer.NumberFormatLocalCounter = 1DoSaveFormat = Buffer.NumberFormatLocalDummy = Buffer.NumberFormatLocalDoEventsSendKeys {tab 3}{down}{enter}Application.Dialogs(xlDialogFormatNumber).Show DummynFormat(Counter) = Buffer.NumberFormatLocalCounter = Counter + 1Loop Until nFormat(Counter - 1) = SaveFormatReDim Preserve nFormat(0 To Counter - 2)Range(A1).Value = Custom formatsRange(B1).Value = Formats used in workbookRange(C1).Value = Formats not usedRange(A1:C1).Font.Bold = TrueStartRow = 3EndRow = 16384For Counter = 0 To UBound(nFormat)Cells(StartRow, 1).Offset(Counter, 0).NumberFormatLocal = nFormat(Counter)Cells(StartRow, 1).Offset(Counter, 0).Value = nFormat(Counter)Next CounterCounter = 0For Each Sh In ActiveWorkbook.WorksheetsIf Sh.Name = CustomFormats Then Exit ForFor Each c In Sh.UsedRange.CellsfFormat = c.NumberFormatLocalIf Application.WorksheetFunction.CountIf(Range(Cells(StartRow, 2), Cells(EndRow, 2)), fFormat) = 0 Then