In this guide, we’re going to show you how to autofit columns using macros in Excel.
Autofit Method
You can use the AutoFit feature by calling the method with same name: AutoFit. The AutoFit method is defined under the Range object, which represents a cell, row, column, or selection of cells. To AutoFit columns using macros, you need to express the columns you want to autofit as the range object. Below are some examples.
| Worksheets("Sheet1").Columns("B:F").AutoFit | AutoFits columns through B to F in Sheet1. |
| Worksheets("Sheet1").Range("A1:E1").Columns.AutoFit | AutoFits columns through A to E in Sheet1. |
| Worksheets("Sheet1").Columns(5).AutoFit | AutoFits the column 5 (E) in Sheet1. |
| Worksheets("Sheet1").Cells.EntireColumn.AutoFit | AutoFits all the column in Sheet1. |
Each command above ends with the AutoFit method. Note that the Columns method can return a range object you need to run the AutoFit method.
How to create a macro to autofit columns using macros
Let's review the basics of creating macros. To start, you need to open the VBA (Visual Basic for Applications) window and add a module. A module is where you write code.
- Press Alt + F11 to open the VBA window.
- In the VBA window, click Insert on the toolbar.
- Click the Module option.
- Create a subroutine by typing in Sub MyAutoFit and pressing Enter (MyAutoFit is just a placeholder, feel free to change it).
- Enter your code between Sub MyAutoFit and End Sub (End Sub will be created automatically upon Enter key.).
Remember to save your workbook as an XSLM (Macro enabled workbook) after entering your code. Otherwise, your code will not be saved.
