45IT.COM- 电脑学习从此开始!
DIY硬件教程攒机经验装机配置
设计Photoshop网页设计特效
系统注册表DOS系统命令其它
存储主板显卡外设键鼠内存
维修显卡CPU内存打印机
WinXPVistaWin7unix/linux
CPU光驱电源/散热齐乐娱乐其它
修技主板硬盘键鼠齐乐娱乐光驱
办公ExcelWordPowerPointWPS
编程齐乐娱乐CSS脚本PHP
网络局域网QQ服务器
软件网络系统图像安全

EXCEL不同的单元格格式太多解决方案

电脑软硬件应用网 45IT.COM 时间:2013-07-10 15:37 作者:佚名
现象: EXCEL打开现象如下图: 解决步骤: 1、打开故障文档,点击视图-宏: 2、输入宏名,点创建: 3、在标记的红色方框内(默认是光标在闪动)输入代码: 代码(摘自网络): Sub DeleteUnusedCustomNumberFormat
现象: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 = "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
4、输完代码,点击保存,退出后重新登陆,故障消失!
顶一下
(7)
100%
踩一下
(0)
0%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片
推荐知识
  1. 用Excel建立一套小型人事数据管理系统 首先我们打开一个新的Excel表建立一个人事信息库框架,信息项目的设置您可根据本单位...
  2. 如何用Excel高效制作考勤表 办公室的内勤工作是公司里面上下联系的枢纽,每天会同一大堆琐碎事务打交道。比如同事...
  3. Excel2003/2007如何中删除重复数据、重复行 大家在使用Excel表格总汇大批量数据时,难免会发现一些重复行和重复数据,这时我们就...
  4. Excel表格的基本操作163招技巧 也许你是个非常老练的办公专员,也许你是一位刚入门的新手,此次给大家推荐的《Excel...
  5. Excel 2010手动批量求和全攻略 有时候我们希望在Excel中将一些数据相加或者相乘得出它的和或积。在Excel中无论是求和...
齐乐娱乐