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.
- 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.
- Then, select the Excel Binary Workbook option.
- 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.
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.
- 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.
- By using this code, you can easily remove the unused area in your Excel and more importantly reduces the Excel file size.
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.
- 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.
- 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
- First, right-click on the Excel file.
- Then, select Send to option.
- After that, in the Send to section, select the Compressed(Zipped) Folder.
- It will convert the Excel file into a zip file.
- IT eventually reduces the Excel file size. See the screenshot.
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.
- 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.
- Then, save the workbook.
- It will eventually reduce the Excel file size with macro. See the screenshot.
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.