TL;DR —
AutoFitmeasures the content that exists at the moment it runs and bakes in a fixed size. It is a snapshot, not a live rule: add a longer value later and the column is clipped again. So it belongs on the last line of your macro, after every write. UseColumns("A:D").AutoFitfor width andRows("1:100").AutoFitfor height; when you start from a partial range, go through.EntireColumnor you fit to one row only. Merged cells quietly defeat row AutoFit — that is the number one reason it "does nothing."
' AutoFit measures what is there NOW, so run it AFTER writing the data.
Columns("A:D").AutoFit ' fit those columns to their current longest value
Rows("1:100").AutoFit ' fit those rows (only grows for wrapped or tall text)
Cells.EntireColumn.AutoFit ' fit every used column on the sheet in one line
' Starting from a partial range? Widen the whole column, not just the range:
Range("A1:D1").EntireColumn.AutoFit
AutoFit is the code version of double-clicking the border between two column headers — the gesture
that snaps a column to exactly fit its widest cell. It is one of the most-used lines in any
report-building macro, and also one of the most misunderstood, because people treat it like a setting
that stays correct. It does not. Once you see it as a measurement taken at a single instant, every
odd behavior — clipped columns, rows that will not grow, a call that seems to do nothing — falls into
place.
What you'll learn
- The mental model —
AutoFitis a one-time measurement, not a rule that keeps re-fitting - The one rule that prevents most bugs — run it last, after the data is written
- Width versus height — why
Columns.AutoFitalways moves butRows.AutoFitoften does not - Fitting a partial range with
.EntireColumn, and the whole sheet withCells.EntireColumn.AutoFit - Why merged cells quietly break row AutoFit
- When a fixed width you control beats re-running AutoFit
The mental model: a measurement, not a rule
Picture AutoFit as taking a ruler to the current longest value in a column and locking the width to
it. That is the whole idea — and the key word is current. AutoFit does not install a rule that says
"always be wide enough." It reads the cells one time, sets a fixed width, and walks away. The column is
now an ordinary fixed-width column that happens to fit the data that was there when you measured.
This is why the classic complaint — "I autofit the column but it is clipped again" — is not a bug. You
measured, then the data changed. A cell that later receives a longer string, a bigger number, or a
wider date format overflows the width you locked in, and Excel shows ####. AutoFit did exactly what
you asked; it just asked a question about the past. Hold on to this and the rest of the article is
common sense.
The rule that matters most: AutoFit runs last
Because AutoFit measures the current content, the single most common mistake is running it before the content exists:
Columns("A:D").AutoFit ' WRONG: the columns are still empty - fits to nothing
' ... code that writes headers and data into A:D ...
Here the macro fits four empty columns to a hairline width, then fills them, and everything is clipped. The fix is a discipline: AutoFit is the last thing a macro does to a range, never the first. Write the headers, write the data, apply the number formats, then AutoFit. If a routine both writes and formats, put the AutoFit call at the very end so it measures the finished product. The same rule applies inside a build loop — do not AutoFit each row as you write it; write all the rows, then AutoFit the block once.
Width always moves; height often does not
Columns.AutoFit and Rows.AutoFit feel symmetric but behave differently, and knowing why saves an
afternoon. Column AutoFit measures the width of the longest value and there is almost always
something to fit, so it visibly moves every time. Row AutoFit measures the height the content needs —
and a normal single-line cell needs exactly the default height, so row AutoFit appears to "do nothing."
Row height only grows when a cell is genuinely taller than one line: text with wrapping turned on, text
with a hard line break (Chr(10)), or a larger font. That is the real connection between AutoFit and
Wrap Text: you turn wrapping on so text spills onto several lines within a
fixed column width, and then Rows.AutoFit has a taller shape to measure and grows the row to reveal
it. Autofit a row height without wrapping and a fixed width in place, and you will keep wondering why
nothing happens.
Fitting a partial range, and the whole sheet
AutoFit widens columns, but it lets you call it from a range — and that is a trap. Called on a
partial range, it fits the columns using only the cells in that range:
Range("A1:D1").AutoFit ' fits A:D using row 1 only - usually far too narrow
Range("A1:D1").EntireColumn.AutoFit ' fits A:D using every cell in those columns
The first line measures just the header row and ignores the data below it, so the result is nearly
always too tight. Reach for .EntireColumn (or .EntireRow for heights) whenever your starting point
is a block of cells rather than a whole column. To fit the entire sheet in one statement, use
Cells.EntireColumn.AutoFit — Cells with no arguments is every used cell, and .EntireColumn lifts
that to every column that contains data. It is the tidy one-liner most report macros end on.
Where AutoFit quietly does nothing: merged cells
The most confusing failure has nothing to do with your code being wrong. Row AutoFit ignores rows
that contain merged cells. It is a long-standing Excel limitation: a merged cell can span several
columns, Excel cannot decide which column should absorb the height, so it declines to resize the row at
all. Turn on wrapping in a merged title cell, call Rows.AutoFit, and the row stays one line tall with
the text clipped — no error, no warning.
If a report leans on merged cells for headers, you cannot rely on row AutoFit there; you have to set an
explicit RowHeight you calculate yourself, or avoid merging (a "center across selection" horizontal
alignment gives the look without the merge). This is one more reason
merged cells cause more trouble than they are worth in anything a macro has to
maintain.
When a fixed width beats AutoFit
AutoFit is perfect for a final snapshot — you have built a report, the data is settled, and you want
each column sized once before you hand it over or export it. It is the wrong tool for a living sheet.
On a dashboard whose numbers refresh, re-running AutoFit on every change makes columns jump around as
values grow and shrink, and AutoFitting thousands of columns on every edit is genuinely slow. There,
you want a rule, not a snapshot: set an explicit
column width you control (Columns("A:D").ColumnWidth = 14) so the layout
stays stable no matter what the data does. The judgment is simple: AutoFit when the content is finished
and you want it to fit once; set a fixed width when the content keeps changing and you want the layout
to hold.
How ExcelMaster helps
AutoFit looks trivial and then quietly costs you an afternoon — the call sits above the write instead of
below it and fits empty columns, a partial range fits to the header row only, or a merged title refuses
to grow and the text is clipped in the copy that goes out. None of it raises an error, so you find out
when someone sees ####.
ExcelMaster lets you say what you
want — "fit every column to its data," "make this description column wrap and grow to fit" — and it
writes the AutoFit at the end of the routine where it belongs, reaches through .EntireColumn when you
start from a range, pairs wrapping with row AutoFit so tall text actually shows, and warns you when a
merged cell means AutoFit cannot do the job. You keep the workbook and the code.
Frequently asked questions
Why is my VBA AutoFit not working?
Almost always one of three things. Either you ran AutoFit before writing the data, so it measured
empty cells — move the call to the end of the routine. Or you called it on a partial range, so it fit
the columns to just those few cells — use .EntireColumn.AutoFit instead. Or the row contains a merged
cell, and row AutoFit ignores merged rows by design — set an explicit RowHeight there instead.
How do I AutoFit all columns on a sheet in VBA?
Use Cells.EntireColumn.AutoFit. Cells with no arguments refers to every used cell, and
.EntireColumn lifts that to every column that holds data, so the whole sheet is fitted in one line.
For rows as well, add Cells.EntireRow.AutoFit. To limit it to a block, name the columns:
Columns("A:H").AutoFit.
What is the difference between Columns.AutoFit and Rows.AutoFit?
Columns.AutoFit sets each column width to its longest value and visibly moves almost every time.
Rows.AutoFit sets each row height to the content it must show, but a normal one-line cell already
fits the default height, so it only changes anything when a cell is taller — wrapped text, a hard line
break, or a larger font. Row height and wrapping go together.
Why does AutoFit not change the row height?
Because the content still fits on one line. Row height only grows for genuinely tall content. Turn on
WrapText = True (with a fixed column width so the text has something to wrap against), or use a hard
line break, and then call Rows.AutoFit. If the row contains a merged cell, AutoFit will not resize
it at all — that is a separate Excel limitation.
Should I AutoFit or set a fixed column width?
AutoFit when the data is finished and you want each column sized once — a report you are about to export
or hand over. Set a fixed ColumnWidth when the sheet keeps changing, such as a dashboard that
refreshes: a fixed width keeps the layout stable, avoids columns jumping around, and is much faster than
re-running AutoFit across many columns on every update.
Tested in
Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-10.
Related guides: VBA Wrap Text · VBA Freeze Panes · VBA Column Width · VBA Merge Cells · VBA Range