Excel VBA AutoFit: Resize Rows and Columns Automatically

In Excel VBA, use the AutoFit method on the rows or columns you want to size. For example, this macro fits columns A through D on a worksheet named Sheet1:

Sub FitColumnsAtoD()
    Dim ws As Worksheet

    ' Fit columns A through D to their contents.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Columns("A:D").AutoFit
End Sub

Before running a macro, save a copy of the workbook: macro actions cannot be reversed with Ctrl+Z. The method is useful for making headings and cell contents visible, but it changes dimensions rather than the underlying values or formulas.

What AutoFit Does in Excel

AutoFit adjusts column width or row height to accommodate the displayed contents of cells. Excel considers the values it shows, including the results of formulas, and the cells’ formatting. A long text entry may need a wider column, while a wrapped paragraph may need a taller row. A date or number format can also affect the width required to display a value.

AutoFit is a one-time sizing action, not a continuing rule. If a later calculation, paste, or edit makes a cell’s displayed content longer, Excel does not necessarily resize that row or column automatically. Run AutoFit again when the data changes, or arrange for a macro to run at a suitable point in your workflow.

Column widths and row heights use different measurements in Excel. Column width is based on character units related to the workbook’s default font; row height is measured in points. As a result, a width value is not a direct pixel measurement, and the same numeric width can look different with different fonts or display scaling.

Fit Columns, Rows, or a Selected Range

For columns, call AutoFit on a column range. For rows, call it on a row range. A rectangular cell range can also be used to identify which columns or rows to fit by selecting its Columns or Rows collection. For example, with data in A1:D20, applying AutoFit to the columns of that range sizes A through D, while applying it to the rows sizes rows 1 through 20.

Excel expects the range passed directly to AutoFit to represent complete rows or complete columns. A call on an arbitrary cell block can cause a run-time error; use .Columns or .Rows when starting from a block of cells. Qualifying the range with a worksheet, as in the macro above, also prevents VBA from applying the change to whichever sheet happens to be active.

To fit every column on a worksheet, use that worksheet’s Columns collection. This can affect columns beyond the visible data area, so it is usually better to target only the columns that contain the table. Similarly, target specific rows if fitting the entire sheet would needlessly alter many blank rows.

Fit Row Heights When Text Wraps

AutoFit is especially useful for wrapped text. First enable wrapping for the relevant cells, then fit their rows. In the following example, notes are in C2:C20 on Sheet1; their rows are resized after wrapping is turned on:

Sub FitWrappedNoteRows()
    Dim ws As Worksheet

    ' Wrap notes, then fit the rows that contain them.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("C2:C20").WrapText = True
    ws.Rows("2:20").AutoFit
End Sub

Wrapping and row fitting do different jobs: WrapText lets a cell’s text occupy multiple lines within its column, while AutoFit adjusts the row height to show those lines. If the column is extremely narrow, the text may wrap into many lines and create very tall rows. Widening the column first can make the result more readable.

Row AutoFit may not calculate the expected height for cells that are merged across columns. Merged cells make it difficult for Excel to determine the available width for wrapped text. If a report depends on predictable row heights, consider unmerging the cells and using another layout approach, such as centering content across a selection, where appropriate.

Run AutoFit on the Data Range Only

Fitting a known table range is usually more controlled than fitting a whole worksheet. For example, with headers in row 1, data in columns A through D, and the last data row identified by a nonblank value in column A, this macro fits just those four columns across the populated rows:

Sub FitTableColumns()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Column A must contain a value for each data row.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A1:D" & lastRow).Columns.AutoFit
End Sub

The last-row calculation starts at the bottom of column A and moves upward to the last nonblank cell. It is a practical pattern when column A is reliably populated for every record. If records can have a blank in A, use a different dependable column or determine the last used row by another method. The code still includes row 1 when the only value in A is the header.

Be cautious with UsedRange. It can include cells that are blank but retain formatting, so a worksheet that once had extensive formatting may have a larger used range than its current table. AutoFitting its columns can therefore affect more columns than expected. A clearly defined range is easier to review and maintain.

Use AutoFit from Excel Without VBA

For a one-time adjustment, the Excel interface is often faster than writing a macro. On Windows, select the columns or rows and double-click the boundary on the right side of a selected column heading or below a selected row heading. You can also select the target area and choose:

Home > Format > AutoFit Column Width to fit columns.
Home > Format > AutoFit Row Height to fit rows.

On Windows, keyboard sequences for the ribbon commands are Alt, H, O, I for AutoFit Column Width and Alt, H, O, A for AutoFit Row Height. These are ribbon key sequences, not a single simultaneous shortcut. On Mac, the ribbon layout and available keyboard shortcuts can differ by Excel release and keyboard settings; double-clicking a heading boundary or using the Home tab’s Format menu are dependable alternatives.

Excel for the web can resize rows and columns through its interface, but it does not run VBA macros. To use VBA, open the workbook in the desktop Excel application. Excel for Mac supports VBA in its desktop application, though some features and automation options differ from Windows.

Common AutoFit Problems and Fixes

The macro reports an error: Check that the worksheet name exists and is spelled exactly as shown. Also make sure AutoFit is applied to rows or columns, or to the Rows or Columns of a cell range, rather than an arbitrary rectangular block.
Text still appears cut off: Confirm that the macro targeted the correct worksheet and columns. Check for merged cells, wrapping settings, and unusually long values. Excel has a maximum column width, so an exceptionally long entry may not fit on one line even after AutoFit.
Rows do not grow enough: Turn on text wrapping for the relevant cells and fit the rows afterward. Merged cells and complicated formatting can prevent a reliable automatic height calculation.
Some columns become much too wide: Inspect the target range for a long note, an unbroken string, or a cell that is not part of the visible table. Fit only the table columns, or use a chosen fixed width for fields that should not expand without limit.
Nothing changes after new data is entered: AutoFit does not continuously monitor edits. Run the macro again after updating the data, or deliberately add event code if automatic resizing is a genuine requirement.

One common misconception is that AutoFit makes all columns the same width. It does not: it calculates a suitable width separately for each targeted column. If consistent widths matter more than fitting every value, set a fixed width instead.

Control Widths After AutoFit

AutoFit and fixed sizing are alternatives, but they can also be combined. A report might first fit its descriptive columns and then give a notes column a chosen width so that one lengthy entry does not dominate the sheet. Excel’s ColumnWidth property sets a width in Excel’s character-based units; RowHeight sets a height in points.

When a column has a maximum or preferred layout width, set that value after fitting, or skip AutoFit for that column. For a large worksheet, a sensible approach is to fit short identifier and date columns while assigning a controlled width to comments. That keeps important values visible without allowing a single unusual entry to make the whole sheet difficult to scan.

AutoFit is also not a substitute for choosing a suitable number format. If a number displays as hashes because its column is narrow, fitting the column may help; if the number has too many decimal places for the purpose, adjusting the number format may be a better presentation choice. Keep the stored value intact unless the data itself needs to change.

Save and Run the Macro Safely

To keep VBA in a workbook, save it in a macro-enabled format such as .xlsm. In desktop Excel for Windows, the Visual Basic Editor can be opened with Alt+F11, and a standard module can be added from the editor’s Insert menu. Paste a complete procedure into the module, change the worksheet name and target range to match the workbook, then run the macro from the editor or Excel’s macro interface.

On a Mac, the Visual Basic Editor and macro controls are available in desktop Excel, but menus and shortcuts may differ. A workbook saved as a regular .xlsx file does not retain VBA code when saved; choose a macro-enabled format if the code needs to remain in the file. Excel for the web can store a macro-enabled workbook but cannot execute its VBA.

Only enable macros in workbooks from sources you trust. A formatting macro can make broad changes if its range is too large, and VBA actions cannot be undone with Ctrl+Z. Save a separate copy before running code, especially when adapting a macro to an unfamiliar workbook.

Frequently Asked Questions

What is the VBA code to AutoFit all columns?
Use a worksheet-qualified call such as ws.Columns.AutoFit, where ws is a worksheet variable set to the sheet you intend to change. For a table, fitting only its columns is usually safer than fitting every column on the sheet.

How do I AutoFit a column in Excel VBA?
Call AutoFit on the specific column or columns, such as ws.Columns("B:B").AutoFit for column B. Replace the range with the worksheet columns that should be adjusted.

How do I AutoFit row height in VBA?
Use a row range, for example ws.Rows("2:20").AutoFit. If the cells contain long text, enable wrapping in those cells before fitting the rows.

Why does AutoFit not work with merged cells?
Excel may not calculate the width available to wrapped text across merged cells in a way that produces the expected row height. Unmerge the cells or use a layout that does not depend on automatic sizing of merged content.

Does AutoFit run automatically when cell contents change?
No. AutoFit adjusts dimensions when it is called; it is not a persistent setting. Run it again after edits, or use a carefully designed event macro if resizing after particular changes is necessary.

Can I use VBA AutoFit in Excel for the web?
No. Excel for the web does not execute VBA. Use its interface to resize rows or columns, or open the workbook in desktop Excel for Windows or Mac to run the macro.

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.