Home ➜ VBA Tutorial ➜ 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 the 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 the cell A1.
As you are within a worksheet so you can also use the columns property and write a code like below.
Columns(1).AutoFit
AutoFit a Row
In the same way, you can write a code to autofit a row. Let’s say you want to autofit the 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 below.
With Worksheets("Sheet1").Cells
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel