|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
各位新年好:
由于表格有公式,一个表格数据太大,甚至有10多MB,导致整个文件夹有几个G,如何批量快速将表格变小,我自己做的只能单个表减小文件大小,而不能批量,请问大神有没有什么办法?
Sub 文档减肥()
Dim FPS
Dim WB As Workbook, SH As Worksheet
Dim I As Long, N As Long, J As Long, X As Long
Dim ARR(1 To 1048576), BRR(1 To 1048576)
FPS = Application.GetOpenFilename("Excel文件,*.xls*", 2, "选择需要减肥的文件", , True)
For I = 1 To UBound(FPS)
Set WB = Workbooks.Open(FPS(I))
For Each SH In WB.Sheets
With SH
For X = 1 To .UsedRange.Columns.Count '遍历列
ARR(X) = .Cells(.Cells.Rows.Count, X).End(xlUp).Row '找最大行
Next
For X = 1 To .UsedRange.Rows.Count '遍历行
BRR(X) = .Cells(X, .Cells.Columns.Count).End(xlToLeft).Column '找最大列
Next
N = Application.Max(ARR) '最大行
J = Application.Max(BRR) '最大列
'这里删除单元格内容
.Range(.Cells(N + 3, 1), .Cells(.Cells.Rows.Count, .Cells.Columns.Count)).Clear '向下清除内容
.Range(.Cells(1, J + 3), .Cells(.Cells.Rows.Count, .Cells.Columns.Count)).Clear
End With
'这里删除无用单元格
Next
Erase ARR '清空数组
Erase BRR '清空数组
WB.Save
WB.Close
Next
MsgBox "清理完成"
End Sub
|
|