Excel VBA Autofit Column Width: How to Do It

VBA & macros · Updated November 22, 2026

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:

  1. Press Alt+F11 (Windows) or Option+F11 (Mac) to open the VBA editor.
  2. In the Project Explorer, right-click the sheet you want (e.g., “Sheet1”) and choose View Code.
  3. Paste a macro like the one below into the code window.
  4. 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.