我有一个仅包含一个工作表的小而简单的*.XLS文件,该工作表只有许多带数字的小文本单元格(文件大小为24Kb)。
但是我进行了很多更改,复制粘贴、扩展公式、保存...之后我删除了大部分这些更改,并创建了4个包含少量数据的工作表副本。
现在我的新文件非常巨大:2.5Mb!
隐藏的数据在哪里,我该如何删除它?
我在真实文件上也遇到了同样的问题,该文件包含300个工作表和每个工作表上的1张图片:文件大小为280Mb。
我有一个仅包含一个工作表的小而简单的*.XLS文件,该工作表只有许多带数字的小文本单元格(文件大小为24Kb)。
但是我进行了很多更改,复制粘贴、扩展公式、保存...之后我删除了大部分这些更改,并创建了4个包含少量数据的工作表副本。
现在我的新文件非常巨大:2.5Mb!
隐藏的数据在哪里,我该如何删除它?
我在真实文件上也遇到了同样的问题,该文件包含300个工作表和每个工作表上的1张图片:文件大小为280Mb。
我使用.XLSB格式存储文件以减小文件大小。 XLSB格式还允许VBA和宏与文件一起保存。通过二进制格式,我曾经将50兆的文件缩小到不到10兆。
Sub ClearExcessRowsAndColumns()
Dim ar As Range, r As Double, c As Double, tr As Double, tc As Double
Dim wksWks As Worksheet, ur As Range, arCount As Integer, i As Integer
Dim blProtCont As Boolean, blProtScen As Boolean, blProtDO As Boolean
Dim shp As Shape
Application.ScreenUpdating = False
On Error Resume Next
For Each wksWks In ActiveWorkbook.Worksheets
Err.Clear
'Store worksheet protection settings and unprotect if protected.
blProtCont = wksWks.ProtectContents
blProtDO = wksWks.ProtectDrawingObjects
blProtScen = wksWks.ProtectScenarios
wksWks.Unprotect ""
If Err.Number = 1004 Then
Err.Clear
MsgBox "'" & wksWks.Name & "' is protected with a password and cannot be checked.", vbInformation
Else
Application.StatusBar = "Checking " & wksWks.Name & ", Please Wait..."
r = 0
c = 0
'Determine if the sheet contains both formulas and constants
Set ur = Union(wksWks.UsedRange.SpecialCells(xlCellTypeConstants), wksWks.UsedRange.SpecialCells(xlCellTypeFormulas))
'If both fails, try constants only
If Err.Number = 1004 Then
Err.Clear
Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeConstants)
End If
'If constants fails then set it to formulas
If Err.Number = 1004 Then
Err.Clear
Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeFormulas)
End If
'If there is still an error then the worksheet is empty
If Err.Number <> 0 Then
Err.Clear
If wksWks.UsedRange.Address <> "$A$1" Then
ur.EntireRow.Delete
Else
Set ur = Nothing
End If
End If
'On Error GoTo 0
If Not ur Is Nothing Then
arCount = ur.Areas.Count
'determine the last column and row that contains data or formula
For Each ar In ur.Areas
i = i + 1
tr = ar.Range("A1").Row + ar.Rows.Count - 1
tc = ar.Range("A1").Column + ar.Columns.Count - 1
If tc > c Then c = tc
If tr > r Then r = tr
Next
'Determine the area covered by shapes
'so we don't remove shading behind shapes
For Each shp In wksWks.Shapes
tr = shp.BottomRightCell.Row
tc = shp.BottomRightCell.Column
If tc > c Then c = tc
If tr > r Then r = tr
Next
Application.StatusBar = "Clearing Excess Cells in " & wksWks.Name & ", Please Wait..."
Set ur = wksWks.Rows(r + 1 & ":" & wksWks.Rows.Count)
'Reset row height which can also cause the lastcell to be innacurate
ur.EntireRow.RowHeight = wksWks.StandardHeight
ur.Clear
Set ur = wksWks.Columns(ColLetter(c + 1) & ":" & ColLetter(wksWks.Columns.Count))
'Reset column width which can also cause the lastcell to be innacurate
ur.EntireColumn.ColumnWidth = wksWks.StandardWidth
ur.Clear
End If
End If
'Reset protection.
wksWks.Protect "", blProtDO, blProtCont, blProtScen
Err.Clear
Next
Application.StatusBar = False
' prepare les combinaison de touches pour la validation automatique de la fenetre
' Application.SendKeys "%(oe)~{TAB}~"
' ouvre la fenetre de compression des images
Application.CommandBars.ExecuteMso "PicturesCompress"
Application.ScreenUpdating = True
End Sub
Function ColLetter(ColNumber As Integer) As String
ColLetter = Left(Cells(1, ColNumber).Address(False, False), Len(Cells(1, ColNumber).Address(False, False)) - 1)
End Function
您可以通过在工作表上使用以下属性来查找此内容:
ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Columns.Count
如果此范围大于您拥有数据的单元格,则删除其余行/列
您会惊讶地发现它可以释放多少空间
XLSM 格式是为使 Excel 符合 Open XML 而设计的,但实际上我们使用 Excel 的 XML 格式的情况非常少。如果不是更多,则可以将大小减小约 50%
例如,如果您需要保存约 10 年的股票价格,并且需要为一只股票保存开盘价、最高价、最低价和收盘价,则会使用 (252*10) * (4) 单元格
相反,可以在单个列中使用字段分隔符 Open:High:Low:Close 来保存开盘价、最高价、最低价和收盘价,而不是使用单独的列。
您可以轻松编写函数以从单个列中提取信息,但这将释放当前占用的近2/3空间。