Teach you how to slim down Excel

Problem Analysis: When you receive an Excel file, found that the file is almost 10M, but in fact, there is not much data in the Excel table, then found that the original is more than a lot of useless cells caused, which involves two concepts: "The last cell in Excel" and "the actual last cell in Excel" , when you press CTRL + END on the keyboard, it will navigate to the cell after the last cell that has the value, or to the cell that is far behind the value, and the extra cell is between the cell with the truth and the position of "ctrl+end" to the last cell, The more it represents the more useless space that Excel occupies, this is why the file becomes larger

Note: You can use the Excel scroll bar to determine

More detailed analysis: http://bbs.51cto.com/thread-882482-1.html

Workaround:

Method One: Manually delete the extra cells

1, find the first cell after the data

2, press the keyboard: Ctrl+shift+end Select all the space behind,

3, press the keyboard: Ctrl +-(minus)

4, the last pop-up in the Delete dialog box to select the entire line, you can really

5, save and exit the file, view the size of the file will be reduced

Method Two: Use the following 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 "to" go "back and forth

Csheet = Wsheet.name

' Rename the sheet to its name with _delete in 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 "columns.count ' find" 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 "Brow ' find" 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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/Office/excel/