How to Reduce Excel File Size with Macro (11 Easy Ways)

Method 1 – Save the Excel File in the Binary Format

Steps:

Go to the File tab in the ribbon and select the Save As option.

Select the Browse option from the Save As section.

Select the Save as type drop-down option.

Select the Excel Binary Workbook option.

Click on Save.
We get a reduced file size compared to macro-enabled format.

Method 2 – Remove Unused VBA Code

Using VBA code can increase the file size to some extent. Removing those unused VBA codes from the Excel file can be an effective solution to reducing Excel file size.

Method 3 – Remove Redundant Code

An optimized code can reduce Excel file size with macro. Suppose you need to bold a cell value by using the VBA code. In most cases, you first select the cell and then bold it in the VBA code.

Sub Bold_cells()
Range("B1").Select
Selection.Font.Bold = True
End Sub

Here we use two lines of code. First, we select the cell and then make the font bold. To remove this redundancy, we can write this code in one line.

Sub Bold_cells()
Range("B1").Font.Bold = True
End Sub

By doing this, we can reduce the code size, and at the same time, we can reduce the overall Excel file size.

Method 4 – Reduce Remark Statements (Comments)

Comments don’t get processed by the compiler, but they still take up space in the file size. Removing comments can reduce the file size slightly.

Method 5 – Exporting All VBA Modules and Importing Them Again

Export all the VBA modules that you created in the past.
Import them back into the VBA modules.

The main advantage of this method is that when you export the VBA modules, it reduces their size. When you import them all in the VBA modules, it also imports with the reduced size.

Method 6 – Disabling Macro

While using VBA code in Excel, we need to save the file in a Macro-Enabled Workbook. Save the workbook with macros disabled, then reopen the workbook. After that, enable the macro.

Method 7 – Optimize the Excel File Size by Removing Unused Areas

Steps

Go to the Developer tab in the ribbon.
Select Visual Basic from the Code group.

It will open the Visual Basic window.
Go to the Insert tab in the ribbon.
Select Module.

In the Module window, use the following code.

Sub RemoveUnused()
    Dim cell_last As Range
    Dim ans
    Dim row_last As Long, column_last As Long, position_of_last_dot As Long
    Dim file_name_full As String, name_of_file As String, target_name As String
    If WorksheetFunction.CountA(Cells) = 0 Then Exit Sub
    Application.ScreenUpdating = False
    ans = MsgBox("Do you need to generate a copy of this Workbook?", vbQuestion + vbYesNoCancel)
    If ans = vbCancel Then
        GoTo ExitSub
    End If
    If ans = vbYes Then
        file_name_full = ActiveWorkbook.FullName
        If file_name_full = "" Then
            MsgBox "Please run the macro after saving the file"
            GoTo ExitSub
        End If
        position_of_last_dot = InStrRev(file_name_full, ".")
        target_name = WorksheetFunction.Replace(file_name_full, position_of_last_dot, 0, "_" & Format(Now(), "yyyymmddhhmmss"))
        ActiveWorkbook.SaveCopyAs target_name
    End If
    row_last = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    column_last = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Application.ScreenUpdating = False
    Rows(row_last + 1 & ":" & Rows.Count).Clear
    Columns(Split(Cells(1, column_last + 1).Address, "$")(1) & ":" & Split(Cells(1, Columns.Count).Address, "$")(1)).Clear
    ActiveWorkbook.Save
ExitSub:
    Application.ScreenUpdating = True
End Sub

Go to the Developer tab in the ribbon.
Select Macros from the Code group.

Select the RemoveUnused macro and click on Run.

Method 8 – Remove Empty Rows and Columns

Blank rows and columns can make the dataset bigger and eventually increase the Excel file to some extent. To reduce Excel file size, we need to remove those empty rows and columns and then save the Excel file.

Method 9 – Remove Data Formatting

Steps

Select the entire dataset or a part from where you want to remove the data formatting.
Go to the Home tab in the ribbon.
Select the Clear option from the Editing group.

Select Clear Formats. It will remove all the formatting from your dataset.

Save the workbook.

Method 10 – Compress the File

Steps

Right-click on the Excel file.
Select the Send to option from the context menu.
In the Send to section, select Compressed (zipped) folder.

It will convert the Excel file into a zip file, which reduces its size.

Method 11 – Compress Images

Steps

Select the image you want to compress.
Go to the Picture Format tab in the ribbon.
Select the Compress Pictures option from the Adjust group.

This will open the Compress Pictures dialog box.
From Compression Options, uncheck the Apply only to this picture option.
Click on Email(96 ppi): minimize document size for sharing.
Click on OK.