- 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:
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.
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:
And if you want to use the row property, then you can use the code like the following.
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.
And if you want to use a specific 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 the 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