Excel VBA宏执行后文件暴增且部分表占用至A1048576求助

• 硬盘故障重装Windows 10（22H2）与Office 365 Business（Excel 2002 Build 12527.22286）后，打开含74工作表、大小17.4MB的xlsm文件，出现以下异常：
  • 大量公式显示#VALUE!，包含@符号、不可编辑的CSE {}数组公式、_xlfn标识
  • 字体混杂Arial与Calibri
• 编写setAllSheetsToDefaultsRemoveEmptyCells宏，目标为移除CSE/@符号、统一字体为Calibri、清理空单元格、删除最后使用行列后的区域，但运行时出现：
  • 内存占用超12GB导致Excel崩溃，添加保存后内存问题解决，但文件体积暴增至264MB
  • 部分工作表被占用到最后一行A1048576（区间单元格为空）
• 当前状态：CTRL+END可正确定位各表最后列，CSE/@/_xlfn已移除，字体恢复，但文件体积暴增问题未解决，尝试过添加保存、延长休眠时间、选中A1、开关计算模式等操作无效

1. 宏逻辑缺陷：原宏可能错误选中整张工作表（而非仅实际使用区域）操作，导致Excel将空白区域标记为已使用，触发全行列格式/属性写入，大幅增加文件体积
2. 隐性冗余数据：重装后Excel对旧文件的兼容性处理，加上宏操作未彻底清理单元格的隐藏格式、条件格式或残留数据验证规则，这些隐性数据占用大量存储空间
3. 公式转换残留：移除CSE数组公式和@符号时，残留无效单元格引用或公式碎片，导致Excel后台维护大量冗余数据

修复宏核心逻辑 #

修改宏，仅针对实际使用区域操作，避免触碰空白全行列：

Sub CleanWorkbook()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim usedRange As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each ws In ThisWorkbook.Worksheets
        ' 获取仅包含内容的实际使用区域
        On Error Resume Next
        Set usedRange = ws.UsedRange.SpecialCells(xlCellTypeConstants + xlCellTypeFormulas)
        On Error GoTo 0
        
        If Not usedRange Is Nothing Then
            lastRow = usedRange.Row + usedRange.Rows.Count - 1
            lastCol = usedRange.Column + usedRange.Columns.Count - 1
            
            ' 统一字体为Calibri
            ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Font.Name = "Calibri"
            
            ' 删除实际使用区域外的空白行列
            If lastRow < ws.Rows.Count Then
                ws.Rows(lastRow + 1 & ":" & ws.Rows.Count).Delete
            End If
            If lastCol < ws.Columns.Count Then
                ws.Columns(lastCol + 1 & ":" & ws.Columns.Count).Delete
            End If
            
            ' 清理使用区域内的空单元格
            ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
        End If
    Next ws
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    ThisWorkbook.Save
End Sub

手动深度清理冗余数据 #

1. 逐个工作表执行：
   • 选中实际最后一行的下一行，按Ctrl+Shift+↓选中所有下方空白行，右键删除
   • 选中实际最后一列的下一列，按Ctrl+Shift+→选中所有右侧空白列，右键删除
2. 打开文件>信息>检查问题>检查文档，勾选"删除个人信息"和"检查隐藏内容"，彻底清理隐性数据
3. 另存为新xlsm文件：选择文件>另存为>Excel启用宏的工作簿，避免原文件格式残留

兼容性优化 #

1. 关闭Excel自动恢复与快速保存：文件>选项>保存，取消勾选"保存自动恢复信息时间间隔"和"快速保存"
2. 转换旧数组公式为动态数组：将原CSE数组公式替换为Office 365支持的动态数组公式（无需按CSE键），减少兼容性残留

内容的提问来源于stack exchange，提问作者Uncl Scott