Ответ 1
Я написал файл VBA, чтобы добавить инструмент, очищающий этот ненормально большой файл. Этот script очищает все столбцы и строки после последних ячеек, реально используемых для reset последних ячеек ([Ctrl] + [End]), а также обеспечивает разрешение сжатия изображений.
Я создаю AddIns с автоматической установкой (просто запустите его с включенным макросом), чтобы включить в контекстное меню много новых кнопок:
- Оптимизация
- Оптимизировать и Сохранить
- Отключить оптимизатор
Это основано на KB of Microsoft office 2003 и ответе PP. с улучшением персоналий:
- добавить сжатие изображений
- Исправить ошибку для столбцов
- совместимость с excel 2007 - 2010 -... (более 255 столбцов)
РЕШЕНИЕ > вы можете скачать мой *.xlam файл ToolsKit
основной код
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