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

While using the VBA code in Excel, we can find out that the Excel file size increases to some extent. For a large Excel file, it is difficult to work on because it may slow down your Excel functioning. That’s why Excel reduction is a must. In this article, we will show how to reduce Excel file size with macro. I hope you find this article informative and solve lots of problems regarding file reduction.

How to Reduce Excel File Size with Macro: 11 Easy Ways

To reduce Excel file size with macro, we have found 11 different ways through which you can solve your problems. All of these methods are fairly useful to use. You can try all of these methods and find the most effective one for you. Some small tricks can solve a big problem.

1. Saving Excel File in Binary Format

The first and most effective method is to save the file in binary format. By using this file format, you can reduce Excel file size to some extent. To understand the method properly, follow the steps.

Steps:

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

• Select the Browse option from the Save As section.

• Now, we need to change the file type.
• As we have a Macro-Enabled Workbook previously, we want to convert it to a Binary Workbook.
• Select the Save as type drop-down option.

• Select the Excel Binary Workbook option.

• After that, click on Save.
• Finally, you will see there is a change in the Excel file size. In Binary format, we get a reduced file size compared to macro-enabled format.

Read More: How to Compress Excel File for Email

2. Remove Unused VBA Codes

Sometimes we have created some VBA codes but they have no use for that Excel file. As we know 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. Unused VBA codes can create some additional problems. It can slow down the Excel’s functioning. So, to have an effective work environment, you have to remove the unused VBA codes.

3. Removing Redundant Codes

You can optimize your code by removing the 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

So, we write the code in one line. By doing this, we can reduce the code size, and at the same time, we can reduce the overall Excel file size.

4. Reduce Remark Statements

Sometimes we use some remark statements in the VBA code to understand it properly. Because of using these remark statements, we make the VBA code very large. As we know, the large VBA code makes the Excel file size large. In that case, you need to eliminate some remark statements to make the code short. By eliminating remark statements, we can reduce the Excel file size and also reduce the code size.

5. Exporting All VBA Modules and Importing Them Again

Another simple way you can reduce Excel file size is with macro. First, export all the VBA modules that you created in the past. Then again, import them into the VBA modules. The main advantage of this method is that when you export the VBA modules, it reduces their size. Then when you import them all in the VBA modules, it also imports as a reduced size. So, the overall size of the Excel file was reduced to some extent. So, this can be a useful solution to reduce Excel file size.

6. Disabling Macro

While using the VBA code in Excel, we need to save the file in a Macro-Enabled Workbook. Another small trick can reduce your Excel file size. In that case, you need to save the workbook with macro disabled and then reopen the workbook. After that, enable the macro. By doing this, you can eliminate the in-build problem because of which the Excel size increases. This process is very easy but it can be effective in some cases.

7. Optimize Excel File Size by Removing Unused Area

Sometimes you face a certain problem where the Excel file size is very large but there is not much data in there. It can happen because of using data formatting beyond the data range. You can remove it manually or use the VBA code. Because of these unused areas, the Excel file size will increase. To optimize Excel file size by using the VBA code, follow the steps.

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 from there.

• In the Module window, write down 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

• Then, go to the Developer tab in the ribbon.
• Select Macros from the Code group.

• It will open the Macro dialog box.
• From there, select the RemoveUnused macro and click on Run.

• By using this code, you can easily remove the unused area in your Excel and more importantly reduce the Excel file size.

Read More: How to Compress Excel File to Smaller Size

8. Remove Empty Rows and Columns

Another easy way to reduce the Excel file size is to remove blank rows and columns. In our dataset, there can be several empty rows and columns. These 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. As a result, you will see a reduction in Excel file size.

Read More: How to Reduce Excel File Size by Deleting Blank Rows

9. Remove Data Formatting

Data formatting is one of the most popular commands we use in Excel files. The Data formatting consists of background colors, adding borders, or changing the font style. All of these are in the data formatting segment. Because of this data formatting segment, your Excel file size can increase to some extent. To remove data formatting you need to follow the following steps.

Steps

• Select the entire dataset from where you want to remove the data formatting.
• Go to the Home tab in the ribbon.
• After that, select the Clear option from the Editing group.

• Then, select Clear Formats.
• It will remove all the formatting from your dataset.

• Finally, save the workbook.
• Then, you will see some reduction in Excel file size.

10. Compress File

Another way you can reduce Excel file size is by compressing the file into a zip file. When you compress your Excel into a zip file, it will automatically reduce its size up to 10-15% of its original size. Then you can share this Excel file with others.

Steps

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

• It will convert the Excel file into a zip file.
• It reduces the Excel file size. See the screenshot.

Read More: How to Reduce Excel File Size Without Opening

11. Compress Images

If you work with the image in Excel, you need to compress those images. Otherwise, it will increase the Excel file size. Here, you need to focus on the quality of the image must be good while compressing. After compressing those images on the outside of the Excel, you need to compress them in the Excel built-in command. To do this, follow the steps properly.

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.

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

• Then, save the workbook and see file size is reduced.

Read More: How to Reduce Excel File Size with Pictures

Download Practice Workbook

Download the practice Workbook below.

Conclusion

We have shown 11 different and effective methods to reduce Excel file size with macro. All of these are effective and can solve your problem. These methods are basically some Excel tricks through which you can get total control of Excel size. I hope we covered all possible areas of Excel file size reduction. If you have further questions, feel free to ask in the comment box.