Autofit VBA Range: Columns, Rows, and Examples

VBA & macros · Updated November 13, 2026

In Excel VBA, use the AutoFit method to size worksheet columns or rows to fit their contents. For a cell range, call EntireColumn.AutoFit or EntireRow.AutoFit; for example, Range("A1:D20").EntireColumn.AutoFit adjusts columns A through D.

What AutoFit means in VBA

AutoFit asks Excel to calculate a suitable width for columns or a suitable height for rows, based on the content and formatting in the cells. It does not change the font size or wrap text automatically. The result is a layout adjustment: a longer heading may make its column wider, while a row containing wrapped text may become taller.

The method is available on Excel’s Range object. Although it is often described as autofitting a range, Excel needs to know whether you mean the rows or columns that contain that range. That is why the explicit forms EntireColumn.AutoFit and EntireRow.AutoFit are useful when starting from a block of cells.

AutoFit is not the same as making every column the same width, fitting a worksheet onto printed pages, or reducing text until it fits. It sizes each selected column or row according to Excel’s content-based calculation. If your goal is a uniform layout, set the width or height directly instead.

Basic syntax and the right range

The simplest column command is Columns("A:D").AutoFit, which fits columns A through D on the active worksheet. To fit rows 1 through 20, use Rows("1:20").AutoFit. Qualifying the range with a worksheet object makes the destination clear and prevents the macro from changing whichever sheet happens to be active.

For a rectangular cell range, select the entire rows or columns represented by the cells before calling AutoFit. For example, Range("A1:D20").EntireColumn.AutoFit fits A:D, while Range("A1:D20").EntireRow.AutoFit fits rows 1:20. These calls operate on whole columns or rows, not merely the individual cell edges inside the rectangle.

A safe general pattern uses a worksheet variable:

Sub AutoFitReport()
    Dim ws As Worksheet

    ' Work on the named worksheet, not the active sheet.
    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Columns("A:D").AutoFit
    ws.Rows("1:20").AutoFit
End Sub

Before running a macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then run the procedure from the editor or assign it to a button. On Mac, Option+F11 opens the editor on many keyboards; you may need Fn as well. Excel for the web does not run VBA macros.

Autofit a range of cells

When a macro already has a target cell range, use its entire columns or rows. This is especially handy when the target address is calculated or passed to a procedure, because the same cell selection can drive either dimension:

Sub FitSelectedReportArea()
    Dim ws As Worksheet
    Dim target As Range

    ' Fit the rows and columns covered by the report area.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set target = ws.Range("A1:D20")
    target.EntireColumn.AutoFit
    target.EntireRow.AutoFit
End Sub

The column call can be affected by content in the selected columns outside rows 1:20, because it fits the whole columns. Similarly, the row call applies to the whole selected rows, and their height reflects the cells across those rows. This is an important distinction: using a small cell range does not limit AutoFit’s calculation to only those cells once you invoke EntireColumn or EntireRow.

If you truly need to size based only on a specific subset, consider copying or calculating the relevant content in a temporary area, or set the desired width and height explicitly. Avoid implying that Range("A1:D20").EntireColumn.AutoFit examines only A1:D20; it adjusts full worksheet columns A:D.

Choose columns, rows, or the used area

Choose the scope deliberately. Fitting just the columns that contain a report is usually faster and less disruptive than fitting every column on a sheet. For example, a table in A:F can be fitted with ws.Columns("A:F").AutoFit. A title or notes section might need selected rows fitted separately, rather than changing heights throughout the worksheet.

To fit columns that Excel considers used on a worksheet, a common approach is ws.UsedRange.EntireColumn.AutoFit. The used range may be larger than the visible table if cells were previously formatted or edited far below or to the right of it, so check the resulting scope when a sheet has a long history. For a predictable report, naming its actual columns is often clearer.

Fitting every column with ws.Cells.EntireColumn.AutoFit can be excessive: a worksheet has many columns, and most may be empty. It can take longer and may change columns that are intended to remain narrow. Use a bounded range unless there is a good reason to process the whole sheet.

For a workbook with several worksheets, loop over the intended sheets rather than relying on the active worksheet. For instance, a macro can set a worksheet variable for each sheet and fit a named table area. Be cautious with blanket workbook-wide changes: sheets may have different layouts, protected states, or carefully designed column widths.

How content and formatting affect the result

AutoFit responds to what Excel displays in cells, including formula results, font characteristics, and number formats. A formula returning a long name can widen a column even though the formula itself is short. A date or currency format can also affect the displayed width. If a numeric value shows as a row of hash marks because the column is too narrow, fitting the column often reveals the formatted value.

Text in an unwrapped cell may visually spill into adjacent empty cells, but that spill is not a reliable indication that the column is wide enough for the cell’s own content. AutoFit sizes for the cell’s value and formatting. If the neighboring cell contains data, the text cannot spill across it, so the layout may look different even though the underlying column width has not changed.

For paragraphs or long descriptions, turn on wrapping before fitting row height. In VBA, set WrapText on the intended range, then fit the rows. Otherwise Excel may prefer a very wide column instead of a taller row. For example, with a target range already assigned to a Range variable named target, the sequence is target.WrapText = True followed by target.EntireRow.AutoFit.

AutoFit does not promise a visually ideal result. Headers can become unusually wide because of one long label, and columns may look inconsistent when they contain different kinds of values. It is often best to autofit first, then apply a deliberate maximum or manually chosen width for presentation.

Common problems and limitations

  • Merged cells: Excel does not reliably AutoFit rows or columns based on merged-cell content. A merged heading may be clipped even after an AutoFit call. For predictable results, avoid merging cells in data areas, use a centered-across-selection layout where appropriate, or set the needed dimensions explicitly.
  • Wrapped text: A row may not become tall enough if wrapping is off or if the relevant rows were not included. Set wrapping on the cells and autofit the corresponding entire rows.
  • Hidden rows or columns: AutoFit is not an instruction to unhide anything. Hidden dimensions remain hidden; unhide them separately if the content needs to be visible.
  • Unexpectedly wide columns: A long value, a formula result, or content far below the visible table in a selected column can control its width. Narrow the target columns or limit the data included before fitting.
  • Protected worksheets: Worksheet protection can prevent changes to row heights or column widths, depending on the protection settings. Check the sheet’s protection configuration before assuming the VBA range address is wrong.

Excel also imposes maximum row heights and column widths. If a cell contains exceptionally long content, AutoFit cannot expand beyond those limits; the full value may still be difficult to read. Consider wrapping, splitting the content, or showing the complete text in the formula bar or a separate detail area.

Control the result after AutoFit

For polished reports, AutoFit can be a first pass rather than the final formatting step. You can cap a column after fitting it, so ordinary values get enough room but one unusually long entry does not dominate the sheet:

Sub FitNameColumnWithLimit()
    Dim ws As Worksheet
    Dim nameColumn As Range

    ' Fit column B, then apply a maximum width in Excel column-width units.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set nameColumn = ws.Columns("B")
    nameColumn.AutoFit
    If nameColumn.ColumnWidth > 35 Then nameColumn.ColumnWidth = 35
End Sub

ColumnWidth is measured in Excel’s character-based column-width units, not pixels. Its apparent pixel width depends on the workbook’s font and display settings. Row heights use points. These units are different, so a value such as 35 for a column should not be interpreted as 35 pixels.

Another option is to set a fixed width or height when consistent layout matters more than fitting every value. For a block with wrapped descriptions, a fixed column width plus fitted row heights can be more readable than letting AutoFit create extremely wide columns. Consider freezing panes or adjusting print settings separately if the sheet must also be easy to navigate or print; AutoFit does not handle those tasks.

When to run the macro

A one-time macro is suitable for a report that is refreshed manually. If data changes frequently, run the fitting procedure after the data import or report-building code has finished writing values and applying formats. Running AutoFit too early means later content may exceed the calculated dimensions.

You can also call a fitting procedure from another VBA procedure, but avoid triggering it repeatedly for every individual cell update. Repeated resizing adds unnecessary work and can make a macro appear slow. A single call after a batch of changes is usually more efficient and produces a stable final layout.

Events such as worksheet-change handlers can automate resizing, but they require care: a change event may fire for a large paste, and code that edits cells can trigger events again unless managed properly. For many workbooks, an explicit button or a call at the end of the refresh macro is easier to understand and maintain.

Frequently Asked Questions

What is the VBA code to autofit a range?
For columns, use Range("A1:D20").EntireColumn.AutoFit; for rows, use Range("A1:D20").EntireRow.AutoFit. Qualify the range with a worksheet, such as Worksheets("Report").Range("A1:D20").EntireColumn.AutoFit, to avoid changing the active sheet by mistake.

Can I autofit a column without selecting it?
Yes. A direct reference such as Worksheets("Report").Columns("B:B").AutoFit works without selecting or activating the worksheet. Referencing objects directly is generally more dependable than using Select or Selection.

How do I autofit row height for wrapped text?
Turn on wrapping for the cells, then call EntireRow.AutoFit on the rows that contain them. Merged cells are a notable exception: AutoFit often cannot calculate their required height correctly, so use an alternative layout or set the height manually.

Why does AutoFit make a column too wide?
A long value or formula result anywhere in the full column can determine its width, even when you started from a smaller cell range and used EntireColumn. Fit only the necessary columns, cap the width after fitting, or choose a fixed width and wrap the text.

Can Excel for the web run an AutoFit VBA macro?
No. Excel for the web can open macro-enabled workbooks, but it cannot run VBA macros. Use Excel desktop on Windows or Mac to run the VBA procedure; ordinary column and row sizing is also available through the desktop interface without a macro.

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.