In this guide, we’re going to show you how to autofit columns using macros in Excel.

Download Workbook

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").AutoFitAutoFits columns through B to F in Sheet1.
Worksheets("Sheet1").Range("A1:E1").Columns.AutoFitAutoFits columns through A to E in Sheet1.
Worksheets("Sheet1").Columns(5).AutoFitAutoFits the column 5 (E) in Sheet1.
Worksheets("Sheet1").Cells.EntireColumn.AutoFitAutoFits 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.

You can find detailed information about referring a range in How to refer a range or a cell in Excel VBA.

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.

  1. Press Alt + F11 to open the VBA window.
  2. In the VBA window, click Insert on the toolbar.
  3. Click the Module option.
  4. Create a subroutine by typing in Sub MyAutoFit and pressing Enter (MyAutoFit is just a placeholder, feel free to change it).
  5. Enter your code between Sub MyAutoFit and End Sub (End Sub will be created automatically upon Enter key.).
    How to autofit columns using macros in Excel 02

Remember to save your workbook as an XSLM (Macro enabled workbook) after entering your code. Otherwise, your code will not be saved.