Excel Macro AutoFit Column Widths: VBA Code and Tips

To AutoFit column widths with an Excel macro, use VBA’s AutoFit method: select the cells or columns to adjust, then run a macro that applies Selection.EntireColumn.AutoFit. The example below fits the columns touched by your current selection; a second version fits the columns in the active sheet’s used range.

What AutoFit does in Excel

AutoFit adjusts a column’s width to accommodate the widest displayed entry in that column. It considers cell contents as they appear in the worksheet, including the displayed results of formulas. If a formula returns a long text value, Excel sizes the column for that result rather than for the formula’s characters. If the result changes later, the column does not necessarily resize automatically; run AutoFit again when you want to fit the new result.

Column width is not a simple measurement in pixels. Excel’s width units are based on the width of characters in the workbook’s Normal style, so the same width setting can look different with another font or display scaling. AutoFit chooses a width that can show the relevant content, subject to Excel’s column-width limit. It is a convenient sizing operation, not a promise that every cell will be readable in every layout.

AutoFit works on columns, while row height is a separate setting. A long paragraph in a cell may be better displayed by wrapping the text and adjusting the row height instead of making the column extremely wide. For wrapped text, use WrapText and fit row heights as appropriate; changing the column width can alter where the text wraps.

Macro for the selected columns

This version adjusts every column that intersects the current selection. You can select one cell, a range of cells, or several whole columns before running it. If you select a cell range such as B2:D20, the entire B, C, and D columns are fitted, not just the selected rows.

Before running a macro, save a copy of the workbook. VBA actions cannot be reversed with Ctrl+Z, so keep a backup if the current widths matter. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this code:

Sub AutoFitSelectedColumns()
    Dim targetRange As Range

    ' Stop if the current selection is not a cell range.
    If TypeName(Selection) <> "Range" Then Exit Sub

    Set targetRange = Selection
    targetRange.EntireColumn.AutoFit
End Sub

Return to Excel, select the target cells, and run the macro from the Visual Basic Editor with the cursor inside the procedure, or use Developer > Macros, select AutoFitSelectedColumns, and choose Run. The TypeName check prevents the procedure from trying to treat a chart or another non-cell selection as a range. The macro changes widths on the active worksheet only.

Macro for the active worksheet’s used columns

If you want to fit all columns Excel regards as used on the active worksheet, use this alternative. It is useful after importing data or refreshing a report when you want a single command to tidy the data area. It does not fit every one of Excel’s 16,384 columns; it targets the columns in the worksheet’s used range.

Save a copy first for the same undo limitation. Add this procedure to a standard VBA module:

Sub AutoFitUsedColumns()
    Dim ws As Worksheet

    ' Work on the currently active worksheet.
    Set ws = ActiveSheet
    ws.UsedRange.Columns.AutoFit
End Sub

Check which sheet is active before running it. If the workbook’s used range has expanded because cells far below or to the right of the visible data were formatted in the past, Excel may consider those distant columns used too. In that situation, fitting the selected data range is more controlled. A macro that changes the active sheet is not the same as a workbook-wide operation: to fit several sheets, you would need to loop through the intended worksheets deliberately.

Run a macro and save the workbook

On Windows, the Developer tab may need to be enabled through File > Options > Customize Ribbon, then checking Developer. From there, Developer > Visual Basic opens the editor and Developer > Macros lists available procedures. You can also assign a macro to a button or a keyboard shortcut, but avoid assigning a shortcut that you already use for an important Excel command.

Save a workbook containing VBA in an enabled format, usually Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, VBA code is not retained in that file. Excel may display a security warning when opening a macro-enabled workbook. Only enable macros from sources you trust; do not lower macro security globally just to make a single sizing macro run.

Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Open the workbook in the desktop Excel application to use these procedures. In Excel for Mac, VBA is available in the desktop app; the Visual Basic Editor can be opened from the Tools menu or the Developer tab. Keyboard shortcuts can depend on the Mac keyboard and function-key settings, so use the menu path if a Windows shortcut does not apply.

Use the ribbon or mouse instead of VBA

A macro is not necessary for a one-time adjustment. Select the relevant columns or cells and choose Home > Format > AutoFit Column Width. You can also select one or more columns and double-click the boundary on the right edge of a selected column header. Excel fits the selected columns to their contents. To fit all columns on a sheet, click the worksheet’s Select All button at the top-left corner of the grid, then use the AutoFit command.

On Windows, the ribbon sequence Alt, H, O, I opens the AutoFit Column Width command through KeyTips. This is a sequence of key presses rather than a shortcut to type into a cell. Menu and ribbon labels can differ slightly by platform or interface version; on Mac, use the Home tab’s Format controls or double-click the column boundary. These built-in methods are useful when you do not want to store or enable VBA.

For a repeatable workflow, a macro saves time and applies the same operation consistently. For occasional formatting, the ribbon command is simpler and avoids macro security prompts, file-format considerations, and the need to maintain code.

Common AutoFit problems and limitations

Merged cells: AutoFit does not reliably size columns to display the full contents of merged cells. A merged cell spans multiple columns, so Excel cannot determine a single column width in the same way it can for an ordinary cell. Consider unmerging the cells, using Center Across Selection for visual alignment, or setting a practical width manually.
Very long entries: A single unusually long value can make its whole column much wider than the rest of the table. Wrap the text, shorten the displayed value, or set a maximum width manually if the sheet needs to remain compact. AutoFit does not decide which entries are important to show.
Dates, numbers, and hashes: A narrow column may display number signs instead of a numeric value or date. AutoFit can often widen the column to reveal the formatted value, but the cell’s number format still determines what is displayed. If the underlying value is not a valid date or the number format is unexpected, width alone will not fix it.
Formula results that change: AutoFit sizes the displayed result at the time you run the command. If a later calculation produces a longer result, rerun the macro or use a suitable fixed width. AutoFit is not a continuous resize setting.
Used range larger than the data: Old formatting or prior edits can leave Excel’s used range broader than the visible table. The whole-used-range macro may therefore affect extra columns. Select the actual table before running the selected-columns macro when you want tighter control.

AutoFit also does not guarantee that a sheet will print on one page, fit a particular screen, or preserve a dashboard layout. Column widths interact with page orientation, scaling, margins, and other layout choices. For a report, check Print Preview after fitting and adjust page setup separately if needed.

AutoFit compared with a fixed width

AutoFit is a good choice for changing or unknown content: imported lists, names, product descriptions, and monthly reports whose values vary. A fixed width is often better for a stable template, a dashboard, or a table that must retain a consistent visual grid. AutoFit can produce uneven columns if one value is much longer than the others, while a fixed width gives predictable layout but may clip or wrap content.

A practical approach is to AutoFit first, then review outliers and set specific widths for columns that become unwieldy. You can also enable Wrap Text for descriptive fields and keep identifier or date columns compact. If a macro should apply a fixed width instead, VBA can set a column’s ColumnWidth property; that is a different operation from AutoFit and uses Excel’s width units rather than a direct pixel measurement.

Frequently Asked Questions

How do I AutoFit all columns with a macro?

To fit every column in the active worksheet, use ActiveSheet.Columns.AutoFit in a VBA procedure. This can process the full set of worksheet columns, so it may take longer than fitting only the columns containing your data. For routine data sheets, fitting the used range or selecting the relevant table is usually more targeted.

Can I AutoFit a column automatically whenever data changes?

Not with the basic AutoFit command by itself. You can add a worksheet change event procedure to run formatting after edits, but event macros require extra care: they must be placed in the correct worksheet module and designed to avoid repeated events or unwanted resizing. For most workbooks, running a simple macro after pasting or refreshing data is easier to control.

Why does AutoFit not work on merged cells?

A merged cell occupies more than one column, so Excel cannot straightforwardly infer the width that should be assigned to each column in the merged area. AutoFit is designed for ordinary row or column contents. Unmerge the cell or choose a manual layout if its text needs to fit reliably.

Does AutoFit change the row height too?

Column AutoFit changes column width, not row height. To adjust heights, use Home > Format > AutoFit Row Height or the corresponding VBA row AutoFit method. Wrapped text can affect the needed row height, so set the column width and wrapping behavior before fitting rows.

Can I run an Excel VBA AutoFit macro in a browser?

No. Excel for the web does not run VBA macros. Use the desktop Excel app for Windows or Mac to run the macro. If a workbook must be used only in a browser, use the ribbon’s available formatting tools there or consider an alternative automation technology supported by that environment.

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.