终极解决excel“不同单元格格式太多”的问题

《终极解决excel“不同单元格格式太多”的问题》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、终极解决EXCEL“不同单元格格式太多”的问题2009-06-2414:35在格式超过4000的Excel表里录制一个新宏，内容如下所示：SubDeleteUnusedCustomNumberFormats()DimBufferAsObjectDimShAsObjectDimSaveFormatAsVariantDimfFormatAsVariantDimnFormat()AsVariantDimxFormatAsLongDimCounterAsLongDimCounter1AsLongDimCounter2AsLongDimStartRowAsLongDimEndRo

2、wAsLongDimDummyAsVariantDimpPresentAsBooleanDimNumberOfFormatsAsLongDimAnswerDimcAsObjectDimDataStartAsLongDimDataEndAsLongDimAnswerTextAsStringNumberOfFormats=1000ReDimnFormat(0ToNumberOfFormats)AnswerText="Doyouwanttodeleteunusedcustomformatsfromtheworkbook?"AnswerText=AnswerText&Chr(1

3、0)&"Togetalistofusedandunusedformatsonly,chooseNo."Answer=MsgBox(AnswerText,259)IfAnswer=vbCancelThenGoToFinitoOnErrorGoToFinitoWorksheets.Add.Moveafter:=Worksheets(Worksheets.Count)Worksheets(Worksheets.Count).Name="CustomFormats"Worksheets("CustomFormats").ActivateSetBuffer=Range("A2")

4、Buffer.SelectnFormat(0)=Buffer.NumberFormatLocalCounter=1DoSaveFormat=Buffer.NumberFormatLocalDummy=Buffer.NumberFormatLocalDoEventsSendKeys"{tab3}{down}{enter}"Application.Dialogs(xlDialogFormatNumber).ShowDummynFormat(Counter)=Buffer.NumberFormatLocalCounter=Counter+1LoopUntilnFormat(C

5、ounter-1)=SaveFormatReDimPreservenFormat(0ToCounter-2)Range("A1").Value="Customformats"Range("B1").Value="Formatsusedinworkbook"Range("C1").Value="Formatsnotused"Range("A1:C1").Font.Bold=TrueStartRow=3EndRow=16384ForCounter=0ToUBound(nFormat)Cells(StartRow,1).Offset(Counter,0).NumberForm

6、atLocal=nFormat(Counter)Cells(StartRow,1).Offset(Counter,0).Value=nFormat(Counter)NextCounterCounter=0ForEachShInActiveWorkbook.WorksheetsIfSh.Name="CustomFormats"ThenExitForForEachcInSh.UsedRange.CellsfFormat=c.NumberFormatLocalIfApplication.WorksheetFunction.CountIf(Range(Cells(StartRo

7、w,2),Cells(EndRow,2)),fFormat)=0ThenCells(StartRow,2).Offset(Counter,0).NumberFormatLocal=fFormatCells(StartRow,2).Offset(Counter,0).Value=fFormatCounter=Counter+1EndIfNextcNextShxFormat=Range(Cells(StartRow,2),Cells(EndRow,2)).Find("").Row-2Counter2=0ForCounter=0ToUBound