Excel Worksheet Autofit Code for Rows and Columns

How-to basics · Updated November 18, 2026

To AutoFit a worksheet’s used columns and rows with VBA, run ws.UsedRange.Columns.AutoFit and ws.UsedRange.Rows.AutoFit on a worksheet object. The macro below applies both commands to the active sheet, adjusting column widths and row heights to suit their contents.

Basic VBA code for a worksheet

In Excel for Microsoft 365 on Windows, this macro fits the rows and columns in the active worksheet’s used range:

Option Explicit

Sub AutoFitActiveWorksheet()
    Dim ws As Worksheet

    ' Use the worksheet currently selected in Excel.
    Set ws = ActiveSheet

    With ws.UsedRange
        .Columns.AutoFit
        .Rows.AutoFit
    End With
End Sub

The first AutoFit command adjusts column widths; the second adjusts row heights. The commands are separate because Excel treats width and height as different settings. Running both is useful when the sheet contains long entries, wrapped text, or a mixture of short and tall rows.

This version targets the used range rather than every row and column in the worksheet. That is usually a practical scope for a report or data table. The used range is Excel’s estimate of the portion of the sheet that has been used, which can include cells with formatting even when they have no visible values. If formatting was applied far down or across the sheet, the used range can therefore be larger than the visible data.

The macro acts on whichever sheet is active when it runs. If the workbook has several tabs, select the intended tab first. For a fixed target, replace the assignment with a worksheet reference such as Set ws = ThisWorkbook.Worksheets(1). That refers to the first worksheet in the workbook containing the macro, not necessarily the workbook currently in front if the code is stored elsewhere.

What AutoFit changes

AutoFit changes the dimensions of existing worksheet rows or columns to accommodate cell contents. For a column, Excel considers the displayed content of cells in that column and selects a width intended to show it without unnecessary clipping. For a row, it adjusts height to display the cell content, including additional lines when text wrapping is enabled.

AutoFit does not alter the cell values, formulas, number formats, fonts, or alignment settings. It also does not insert or delete rows and columns. It is a sizing operation, so it is generally safe for the data itself, although the resulting layout can affect how a sheet prints or how much of it fits on screen.

  • Column AutoFit changes width to accommodate content in the selected columns.
  • Row AutoFit changes height to accommodate content in the selected rows, including wrapped lines.
  • Both commands are needed when the goal is to adjust the overall worksheet layout in both directions.

AutoFit is not the same as setting every column to one identical width or every row to one identical height. It calculates dimensions from the content, so a column containing a long heading may become wider than a column containing short codes. If uniform sizing is the goal, set a specific width or height instead.

Fit only selected rows or columns

You do not have to resize the entire used range. To fit only columns A through D on the active worksheet, use a targeted version like this:

Option Explicit

Sub AutoFitSelectedColumns()
    Dim ws As Worksheet

    Set ws = ActiveSheet
    ws.Range("A:D").Columns.AutoFit
End Sub

To fit only rows 2 through 25, use ws.Rows("2:25").AutoFit inside a procedure with a declared worksheet variable. A range-based alternative is ws.Range("A2:D25").Rows.AutoFit. The first form targets row heights for those numbered rows; the second identifies the rows through a cell range. Choose a scope that matches the task rather than resizing unrelated parts of a carefully formatted sheet.

For a one-time adjustment, VBA may be unnecessary. Select the relevant columns or rows, then use the ribbon command Home > Format > AutoFit Column Width or Home > Format > AutoFit Row Height. You can also double-click the boundary between column letters or row numbers to AutoFit the selected dimension. In the worksheet grid, selecting several columns or rows first applies the action to all of them.

How to add and run the macro

  1. Save a copy of the workbook before running a macro, especially if the sheet has carefully adjusted widths, heights, or print layout.
  2. In desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, try Option+F11; some keyboards require Fn+Option+F11. You can also open the editor from the Developer tab if it is available.
  3. In the editor, choose Insert > Module, then paste the code into the new module.
  4. Return to Excel, select the worksheet to adjust, and run the macro. On Windows, press Alt+F8, select AutoFitActiveWorksheet, and choose Run. If shortcuts differ on a Mac keyboard, use Excel’s macro list or the Developer tab.
  5. If you need to keep VBA in the workbook, save it as an Excel Macro-Enabled Workbook (.xlsm). An ordinary .xlsx file does not preserve VBA macros when saved in that format.

Excel for the web can open and edit many workbooks, but it does not run VBA macros. Use desktop Excel to create or execute this code. On Windows and Mac, macro security settings may also prevent a macro from running; only enable macros for files and code you trust.

Wrapped text, merged cells, and formatting

For wrapped text, AutoFit row height is often the step that reveals the full entry. If a cell has Wrap Text enabled, Excel can display its contents on multiple lines, and AutoFit can increase the row height to make room. The column width still matters: a narrow column creates more wrapped lines and a taller row, while a wider column may reduce the row height. For a table, it can help to fit columns first and rows second, as in the sample macro.

Merged cells are a common reason AutoFit appears not to work. Excel’s automatic row-height sizing does not reliably calculate the height needed for text in merged cells, and AutoFit may leave the row too short or make no apparent change. If a heading is merged across several columns, consider unmerging it and using Center Across Selection where appropriate, or set the row height manually. That gives more predictable results than expecting AutoFit to measure a merged area.

Other layout choices can affect what you see. A fixed row height may be replaced when AutoFit runs. A wide column may push other information off screen, and a row that fits on screen may still affect page breaks when printed. If the sheet uses custom dimensions for presentation, consider running the macro only on the data area or keeping a copy of the original layout.

Common problems and practical fixes

  • The macro changes the wrong tab: it uses ActiveSheet, so select the correct worksheet before running it. For repeatable automation, refer to a named worksheet in the correct workbook.
  • Some columns become unexpectedly wide: inspect the cells for long text, unusual values, or formatting that extends the used range. A long note in one cell can determine the width of its entire column.
  • The sheet takes longer to resize than expected: formatting applied far beyond the actual data can enlarge UsedRange. Remove unnecessary formatting from distant rows or columns if appropriate, then save and reopen the workbook before judging the used range again.
  • Wrapped text is still cut off: check that Wrap Text is enabled, that the row is not being held to a fixed height, and that the cell is not merged. Run row AutoFit after setting a useful column width.
  • Undo does not restore the layout: Excel macros cannot be undone with Ctrl+Z. Save a copy before running code, or record the original dimensions if you need to restore them.

AutoFit can also produce a width that is technically sufficient but inconvenient for reading. For example, a column containing long web addresses or unbroken identifiers may expand substantially. In that case, a manually chosen width, wrapped text, or a separate display column may be preferable to fitting every value on one line.

When to choose a different method

Use VBA when the same sizing task must be repeated, when you are automating a report, or when a workbook process should resize a sheet after adding data. For a one-off adjustment, the ribbon or a double-click on a row or column boundary is faster and avoids enabling macros.

AutoFit is also not always the right final step for a print-ready worksheet. If consistent widths, fixed-height headings, or page layout are important, set dimensions deliberately and use Print Preview to check the result. A hybrid approach often works well: AutoFit the data columns, then manually limit unusually wide columns and adjust title rows to suit the page.

Keep in mind that these commands set dimensions, not a permanent rule. If more data is entered later, Excel will not necessarily rerun the macro automatically. You can run it again, or place suitable code in a worksheet or workbook event procedure if you understand VBA events and want automatic behavior. Event code needs extra care because it may run during ordinary editing, so a manually launched macro is simpler for many users.

Frequently Asked Questions

What is the VBA code to AutoFit all worksheet columns?
With a worksheet variable named ws, use ws.UsedRange.Columns.AutoFit to fit columns in the used range. To target the active sheet, first assign Set ws = ActiveSheet. Use a full-column scope only when you have a reason to include columns outside the used range.

How do I AutoFit both row height and column width?
Run both commands on the intended worksheet: ws.UsedRange.Columns.AutoFit, followed by ws.UsedRange.Rows.AutoFit. The first adjusts widths; the second adjusts heights. Fitting widths before heights is useful when wrapping affects the number of lines in a cell.

Can I AutoFit a worksheet without VBA?
Yes. Select the relevant rows or columns, then use Home > Format and choose AutoFit Row Height or AutoFit Column Width. Double-clicking a row or column boundary is another quick option for the selected dimension.

Why does AutoFit not resize a merged cell correctly?
Excel does not reliably AutoFit row height for merged cells. Unmerge the cell, use another heading layout, or set the row height manually. This limitation is about Excel’s sizing behavior, not a missing VBA command.

Will the macro run in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the code, and save it in a macro-enabled format if the VBA must remain in the workbook.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.