In this guide, we’re going to show you how to autofit rows 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 Range object which represents a cell, row, column, or selection of cells. To AutoFit rows using macros, you need to express the rows you want to autofit as the range object. Below are some examples.

Worksheets("Sheet1").Rows("1:10").AutoFitAutoFits rows through 1st to 10th in Sheet1.
Worksheets("Sheet1").Range("A5:A20").Rows.AutoFitAutoFits rows through 5th to 20th in Sheet1.
Worksheets("Sheet1").Rows(5).AutoFitAutoFits the row 5 in Sheet1.
Worksheets("Sheet1").Cells.EntireRow.AutoFitAutoFits all the row in Sheet1.

Each command above ends with the AutoFit method. Note that the Rows 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 autofit rows 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.).

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