VBA AutoFit (Rows, Column, or the Entire Worksheet)

Key Points

• In VBA, you can use the AutoFit method to auto-fit rows, columns, and even an entire worksheet.
• You need to specify the range, and then you can use the AutoFit method.

AutoFit a Column

Let’s say you want to autofit column A, the code would be something like below:

Range("A1").EntireColumn.AutoFit

In the above line of code, you have used the EntireColumn property to refer to the entire column of cell A1.

As you are within a worksheet so you can also use the columns property and write a code like the below.

Columns(1).AutoFit

AutoFit a Row

In the same way, you can write code to autofit a row. Let’s say you want to autofit row 5, the code would be:

Range("A5").EntireRow.AutoFit

And if you want to use the row property, then you can use the code like the following.

Rows(5).AutoFit

AutoFit UsedRange (Rows and Columns)

Now let’s say, you only want to autofit those columns and rows where you have data. In VBA, there is a property called used range that you can use. So the code would be.

ActiveSheet.UsedRange.EntireColumn.AutoFit

ActiveSheet.UsedRange.EntireRow.AutoFit

And if you want to use a specific worksheet then the code would be.

Worksheets("Sheet1").UsedRange.EntireColumn.AutoFit
Worksheets("Sheet1").UsedRange.EntireRow.AutoFit

AutoFit Entire Worksheet

And if you want to refer to all the columns and rows of the worksheet then you can use the “CELLS” property. Here’s the code.

Worksheets("Sheet1").Cells.EntireColumn.AutoFit
Worksheets("Sheet1").Cells.EntireRow.AutoFit

Or you can also use VBA’s WITH statement to write a code like the one below.

With Worksheets("Sheet1").Cells
    .EntireColumn.AutoFit
    .EntireRow.AutoFit
End With