To autofit a column’s width in Excel VBA, use the AutoFit method on the EntireColumn property of a range, for example Range("A:A").EntireColumn.AutoFit. This adjusts the column to match the longest visible content in any cell within that column, and it works on a single column, multiple columns, or the entire worksheet in one line of code.
Why Use VBA for Autofit Instead of the Ribbon?
Excel’s built-in autofit, triggered by double-clicking the column boundary or using Home > Format > AutoFit Column Width, works fine for one-off adjustments. But VBA becomes valuable when you need to autofit repeatedly—for example, after every data refresh, on multiple sheets, or as part of a larger macro that formats a report. VBA also lets you control which columns get autofit, skip hidden columns, or apply a maximum width limit, which the ribbon cannot do directly.
Basic Autofit Syntax and Examples
The core syntax is simple: Range("A:A").EntireColumn.AutoFit. The EntireColumn part is optional if you already reference whole columns, but it ensures the method applies to the full column even if you select a partial range. Here are common variations:
- Single column:
Range("B:B").EntireColumn.AutoFit - Multiple columns:
Range("A:C").EntireColumn.AutoFit(autofits columns A, B, and C) - Used range on a sheet:
ActiveSheet.UsedRange.EntireColumn.AutoFit(autofits all columns that contain data) - Entire worksheet:
Cells.EntireColumn.AutoFit(autofits every column on the sheet, including empty ones—usually not needed)
To autofit a column based on a specific range (e.g., only rows 1 to 50), use Range("A1:A50").EntireColumn.AutoFit. This adjusts column A to fit the content in those rows only, ignoring data below row 50.
How to Run the VBA Code
To use VBA, you need the Developer tab. If it’s not visible, go to File > Options > Customize Ribbon and check “Developer.” Then follow these steps:
- Press Alt+F11 (Windows) or Option+F11 (Mac) to open the VBA editor.
- In the Project Explorer, right-click the sheet you want (e.g., “Sheet1”) and choose View Code.
- Paste a macro like the one below into the code window.
- Press F5 to run it, or close the editor and run it from the Macros dialog (Alt+F8).
Here’s a complete macro that autofits columns A through D on the active sheet:
Sub AutofitColumns()
' Autofit columns A to D on the active worksheet
Range("A:D").EntireColumn.AutoFit
End Sub
Before running any macro, save a copy of your workbook (File > Save As) because macros cannot be undone with Ctrl+Z.
Common Problems and Solutions
Autofit sometimes doesn’t work as expected. Here are the most frequent issues and fixes:
- Hidden rows or columns: Autofit ignores hidden rows and columns. If you have filtered data, autofit only considers visible cells. To include hidden data, unhide first or use a loop that temporarily unhides.
- Merged cells: Autofit does not work on merged cells. If a column contains merged cells, the width may not adjust correctly. You must unmerge or handle them separately.
- Wrap text: When wrap text is enabled, autofit adjusts the width to fit the longest line, but the row height may not change automatically in VBA. You may need to also autofit row heights.
- Shapes or charts: Autofit only considers cell content, not shapes or embedded objects. If a chart overlaps a column, you’ll need to adjust manually.
- Very long text: If a cell contains a huge amount of text, autofit may set the column width to the maximum allowed (255 characters). In that case, consider using wrap text or reducing the font size.
Autofit with a Maximum Width Limit
Sometimes you don’t want a column to become too wide. VBA can autofit and then cap the width. For example, to autofit column A but keep it under 30 characters wide:
Sub AutofitWithMax()
Dim maxWidth As Double
maxWidth = 30
' Autofit first
Range("A:A").EntireColumn.AutoFit
' If wider than max, set to max
If Range("A:A").ColumnWidth > maxWidth Then
Range("A:A").ColumnWidth = maxWidth
End If
End Sub
This is useful for reports where readability matters more than showing every character.
Autofit on Specific Ranges or Used Range
To autofit only the columns that contain data, use ActiveSheet.UsedRange. This avoids adjusting empty columns at the far right. Example:
Sub AutofitUsedColumns()
' Autofit all columns that have any data in the used range
ActiveSheet.UsedRange.EntireColumn.AutoFit
End Sub
If you want to autofit only certain rows, like a header row, use Range("A1:F1").EntireColumn.AutoFit. This will fit the columns to the header text, ignoring the data below—useful when headers are longer than data.
Autofit on Multiple Sheets or a Workbook
To autofit all columns on every sheet in the workbook, loop through the sheets:
Sub AutofitAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.UsedRange.EntireColumn.AutoFit
Next ws
End Sub
This is handy for cleaning up messy workbooks before sharing.
Performance Considerations
Autofit is a relatively fast operation, but on very large datasets (hundreds of thousands of rows), it can slow down. To speed up, you can disable screen updating while the macro runs:
Sub FastAutofit()
Application.ScreenUpdating = False
ActiveSheet.UsedRange.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
This prevents the screen from flickering and can make the macro run noticeably faster. Always re-enable screen updating at the end, even if an error occurs (use error handling if needed).
Frequently Asked Questions
Does autofit work on merged cells?
No, autofit does not work on merged cells. If a column contains merged cells, the width may not adjust correctly. You need to unmerge the cells or handle them separately.
Why does autofit not work on hidden rows?
Autofit only considers visible cells. If rows are hidden (e.g., filtered), the column width is based only on visible data. To include hidden rows, unhide them first or use a loop that temporarily unhides.
Can I autofit a column to a specific range of rows?
Yes, use Range("A1:A10").EntireColumn.AutoFit to fit column A based only on rows 1 to 10.
How do I autofit all columns in a worksheet?
Use Cells.EntireColumn.AutoFit or ActiveSheet.UsedRange.EntireColumn.AutoFit. The latter is better because it avoids empty columns.
Does autofit work in Excel for Mac?
Yes, the VBA method works the same on Mac, but the keyboard shortcuts differ (Option+F11 to open the editor). The code itself is identical.
Can I autofit row height as well?
Yes, use Range("1:1").EntireRow.AutoFit for rows. Often you’ll autofit both columns and rows to fit content.
Related
- Excel Sales Tracking Template: Free XLS & How to Use
- Excel Schedule Template: How to Make One
- Excel Sales Tracking Template Weekly: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Find Exact Match: Find Whole-Cell ValuesIn Excel VBA, set the LookAt argument of Range.Find to xlWhole to match an entire cell rather than…
- Read a Text File with Excel VBA: Methods and ExamplesYes. In Excel for Microsoft 365 on Windows, VBA can read a plain-text file with the Open and…
- VBA InputBox Integer: Get and Validate Whole NumbersVBA’s standard InputBox returns text, not an Integer. In Excel VBA, use Application.InputBox with Type:=1 to request a…
- Excel VBA Hidden Worksheets: Hide and Unhide SheetsIn Excel VBA, hide a worksheet by setting its Visible property to xlSheetHidden or xlSheetVeryHidden; set it to…
- VBA AutoFilter Criteria Not Equal: How to Use ItTo filter out a value with VBA AutoFilter, pass a not-equal criterion such as "<>Closed" to the field…
- Macro Recorder for Mac: Options and How to ChooseYes—Mac users can record and replay repetitive actions, but macOS does not provide one universal recorder that captures…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.