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.


Download Practice Workbook

Download the practice Workbook below.


11 Effective Ways to Reduce Excel File Size with Macro

To reduce Excel file size with macro, we have found eleven 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 out the most effective one for you. Some small tricks can solve a big problem.


1. Saving Excel File in Binary Format

The first and the 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

  • First, go to the File tab in the ribbon.
  • Then, select the Save As option.

  • Select the Browse option from the Save As section.

  • Then, 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.

How to Reduce Excel File Size with Macro

  • Then, select the Excel Binary Workbook option.

How to Reduce Excel File Size with Macro

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

How to Reduce Excel File Size with Macro

Read More: How to Compress Excel File for Email (13 Quick Methods)


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 also. I can slow down the Excel 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 also. 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 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 reduces 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 using 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

  • First, go to the Developer tab in the ribbon.
  • Then, select Visual Basic from the Code group.

  • It will open the Visual Basic window.
  • Then, go to the Insert tab in the ribbon.
  • Select Module from there.

  • Then, 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 option.
  • Then, click on Run.

How to Reduce Excel File Size with Macro

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

Read More: Reduce Large Excel File Size by 40-60% (12 Proven Methods)


8. Remove Empty Rows and Columns

Another easy way we can reduce the Excel file size is by removing 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. So, 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.


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

  • First, select the entire dataset from where you want to remove the data formatting.
  • Then, 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.

How to Reduce Excel File Size with Macro

  • 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. To apply this method, follow the steps

Steps

  • First, right-click on the Excel file.
  • Then, select Send to option.
  • After that, in the Send to section, select the Compressed(Zipped) Folder.

How to Reduce Excel File Size with Macro

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

How to Reduce Excel File Size with Macro

Read More: How to Reduce Excel File Size Without Opening (with Easy Steps)


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 that 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

  • First, select the image you want to compress.
  • Then, go to the Picture Format tab in the ribbon.
  • Select the Compress Pictures option from the Adjust group.

  • Then, 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.

How to Reduce Excel File Size with Macro

  • Then, save the workbook.
  • It will eventually reduce the Excel file size with macro. See the screenshot.

How to Reduce Excel File Size with Macro

Read More: How to Reduce Excel File Size with Pictures (2 Easy Ways)


Conclusion

We have shown eleven 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. Don’t forget to visit our Exceldemy page.


Durjoy Paul
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo