现象:EXCEL打开现象如下图:
文章插图
文章插图
【EXCEL不同的单元格格式太多解决方案你知道吗】解决步骤:
1、打开故障文档 , 点击“视图-宏”:
文章插图
2、输入宏名 , 点创建:
文章插图
3、在标记的红色方框内(默认是光标在闪动)输入代码:
文章插图
代码(摘自网络):
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 = http://www.jslnfj.com/dnbaike/27554/"Custom formats"
Range("B1").Value = http://www.jslnfj.com/dnbaike/27554/"Formats used in workbook"
Range("C1").Value = http://www.jslnfj.com/dnbaike/27554/"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 = http://www.jslnfj.com/dnbaike/27554/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 = http://www.jslnfj.com/dnbaike/27554/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 = http://www.jslnfj.com/dnbaike/27554/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 Sub4、输完代码 , 点击保存 , 退出后重新登陆 , 故障消失!
推荐阅读
- 如何在excel公式中运用三维引用成为攻无不克的玩家
- Excel单元格出现####错误,是怎么一回事?2020开心玩
- Excel-2013宏代码锁定含有公式的单元格[图文攻略]
- 以不同颜色显示Excel-2013单元格数据为整数的值图文攻略666
- 高亮显示Excel-2013表格里某个季度的全部数据一览大全
- 怎么让excel的网址能直接点开?科普大全
- 在Excel-2013换行单元格中提取出第二行的数据一览大全
- EXCEL技巧精粹:十年财务积累的EXCEL技巧2020攻略资讯
- Excel无法访问-该文档可能为只读或加密文档2020新发布
- 打开一个Excel模板文件填充数据另存为一个文件2020开心玩