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 below.
With Worksheets("Sheet1").Cells
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
More 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 in VBA
- Apply Borders on a Cell using VBA in Excel
- Find 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 a VBA Code
- 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
⇠ Back to What is VBA in Excel
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder – VBA Interview Questions – VBA Codes