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
Related Tutorials
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find the Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells