How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA

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 the column A, the code would be something like below:


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

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


AutoFit a Row

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


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


Auto Fit UsedRange (Rows and Columns)

Now let’s say, you only want to auto fit 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.



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


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.



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

With Worksheets("Sheet1").Cells
End With