问题分析：当您收到一个Excel文件，发现文件差不多10M，但其实Excel表格里面没多少数据，后来发现原来是多了很多没用的单元格所造成，这里面涉及到两个概念：“Excel 最后的单元格”和“Excel实际的最后的单元格”，当您按键盘的“Ctrl+ End”， 它将会定位到最后有值的单元格后一个单元格，又或者是在远远在有值的单元格的后面，多余的单元格就是在有真值的单元格跟“Ctrl+End”定位到最后的单元格的位置之间，越多就代表Excel占用越多没用的空间，这就是文件变大的原因
注意：可以通过Excel的滚动条判断
更加详细的分析：http://bbs.51cto.com/thread-882482-1.html
解决方法：
方法一
：手动删除多余的单元格
1、找到有数据后的第一个单元格
2、按键盘：Ctrl+Shift+End 选中后面所有的空格，
3、按键盘：Ctrl+ -（减号）
4、最后在弹出的删除对话框选择整行，确实即可
5、保存并退出文件，查看文件的大小就会缩小了
方法二
：使用以下的VBA Macro
----------------------------------------------------------
Option Explicit
Sub SHRINK_EXCEL_FILE_SIZE()
Dim WSheet As Worksheet
Dim CSheet As String 'New Worksheet
Dim OSheet As String 'Old WorkSheet
Dim Col As Long
Dim ECol As Long 'Last Column
Dim lRow As Long
Dim BRow As Long 'Last Row
Dim Pic As Object
For Each WSheet In Worksheets
WSheet.Activate
'Put the sheets in a variable to make it easy to go back and forth
CSheet = WSheet.Name
'Rename the sheet to its name with _Delete at the end
OSheet = CSheet & "_Delete"
WSheet.Name = OSheet
'Add a new sheet and call it the original sheets name
Sheets.Add
ActiveSheet.Name = CSheet
Sheets(OSheet).Activate
'Find the bottom cell of data on each column and find the further row
For Col = 1 To Columns.Count 'Find the actual last bottom row
If Cells(Rows.Count, Col).End(xlUp).Row > BRow Then
BRow = Cells(Rows.Count, Col).End(xlUp).Row
End If
Next
'Find the end cell of data on each row that has data and find the furthest one
For lRow = 1 To BRow 'Find the actual last right column
If Cells(lRow, Columns.Count).End(xlToLeft).Column > ECol Then
ECol = Cells(lRow, Columns.Count).End(xlToLeft).Column
End If
Next
'Copy the REAL set of data
Range(Cells(1, 1), Cells(BRow, ECol)).Copy
Sheets(CSheet).Activate
'Paste Every Thing
Range("A1").PasteSpecial xlPasteAll
'Paste Column Widths
Range("A1").PasteSpecial xlPasteColumnWidths
Sheets(OSheet).Activate
For Each Pic In ActiveSheet.Pictures
Pic.Copy
Sheets(CSheet).Paste
Sheets(CSheet).Pictures(Pic.Index).Top = Pic.Top
Sheets(CSheet).Pictures(Pic.Index).Left = Pic.Left
Next Pic
Sheets(CSheet).Activate
'Reset the variable for the next sheet
BRow = 0
ECol = 0
Next WSheet
' Since, Excel will automatically replace the sheet references for you on your formulas,
' the below part puts them back.
' This is done with a simple replace, replacing _Delete with nothing
For Each WSheet In Worksheets
WSheet.Activate
Cells.Replace "_Delete", ""
Next WSheet
'Roll through the sheets and delete the original fat sheets
For Each WSheet In Worksheets
If Not Len(Replace(WSheet.Name, "_Delete", "")) = Len(WSheet.Name) Then
Application.DisplayAlerts = False
WSheet.Delete
Application.DisplayAlerts = True
End If
Next
End Sub
本文转自 Nico_Lv 51CTO博客，原文链接：http://blog.51cto.com/nearlv/1110659，如需转载请自行联系原作者
