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

- Written by Puneet

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
vba-auto-fit-a-column

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
use-column-property

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
auto-fit-a-row

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

Rows(5).AutoFit
auto-fit-row-code

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
auto-fit-usedrange

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

Worksheets("Sheet1").UsedRange.EntireColumn.AutoFit
Worksheets("Sheet1").UsedRange.EntireRow.AutoFit
auto-fit-usedrange-code

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
auto-fit-entire-worsheet

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
auto-fit-entire-worksheet-code