VBA Column Width & Row Height in Excel — Resize and AutoFit in Code (and the Units That Trip You Up)

TL;DR — Sizing is a property of the whole column or row, and the two dimensions use different units: ColumnWidth is in characters of the Normal font, RowHeight is in points. So ColumnWidth = 10 and RowHeight = 10 are not the same size in any sense. AutoFit is the trap — it only runs on an entire column or row (Columns("A:C").AutoFit or rng.EntireColumn.AutoFit), and it measures displayed text, so it silently ignores merged cells.

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Report")
ws.Columns("A:C").AutoFit                 ' size A:C to their widest content
ws.Columns("D").ColumnWidth = 18          ' 18 characters of the Normal font
ws.Rows(1).RowHeight = 28                 ' 28 points

Resizing looks like the simplest thing in the formatting family, and it mostly is — until an AutoFit does nothing, or a width you set does not match the width you expected. Both come from the same root, the idea this guide is built on: you size the whole column or row, and width and height speak in different units. Hold that, and the read-only .Width property, the AutoFit rules and the "why won't this hide" confusion all resolve.

What you'll learn

The mental model — you size the entire column or row, in two different units
The rule that matters most — AutoFit needs a whole column or row, and measures the display
ColumnWidth (characters) versus the read-only .Width (points)
RowHeight, wrap text, and when a row grows on its own
AutoFit's blind spot — merged cells
Hiding a column the right way, and why width zero is a trap

The mental model: you size the whole column or row, in two units

Width and height do not belong to a cell — they belong to the column and the row it sits in. You can reach them from a cell, but the value applies to the entire line:

ws.Range("D5").EntireColumn.ColumnWidth = 18   ' widens ALL of column D, not just D5
ws.Range("D5").EntireRow.RowHeight = 28        ' heightens ALL of row 5

The second half of the model is the units, and this is the part that trips people. ColumnWidth is measured in characters — specifically, how many 0 digits of the Normal-style font fit in the column (the default 8.43 means about eight zeros). RowHeight is measured in points, the same 1/72-inch unit as font size. The two numbers are not comparable, and there is no clean conversion between them because one depends on the font and one does not. When a layout looks off, it is very often a width value that was reasoned about as if it were points.

The rule that matters most: AutoFit needs a whole column or row

AutoFit is the fastest way to size to content, and it has one firm requirement: it must be called on an entire column or an entire row, never on an arbitrary cell range.

ws.Columns("A:C").AutoFit          ' OK - whole columns
ws.Rows("1:5").AutoFit             ' OK - whole rows
ws.Range("A1:C5").EntireColumn.AutoFit   ' OK - promoted to whole columns first

ws.Range("A1:C5").AutoFit          ' WRONG - a cell block is not a row or column -> error 1004

The fix is always the same: promote the range to whole lines with .EntireColumn or .EntireRow first. The second half of the rule is subtler — AutoFit measures what is displayed, not what is stored. It sizes to the widest rendered text, so a column of numbers shown as "0" fits to 5, not to the underlying 5.4999; and a column whose content is hidden behind a narrow NumberFormat fits to the format, not the value.

ColumnWidth versus the read-only Width

There are two width properties, and only one of them is settable — a common source of "why won't this compile" confusion:

ColumnWidth is settable, measured in characters. This is the one you assign to.
.Width is read-only, measured in points. It tells you the rendered width of a range on screen, but you cannot assign to it — Columns("A").Width = 100 fails.

ws.Columns("A").ColumnWidth = 20          ' set it - characters
Debug.Print ws.Columns("A").Width          ' read it - points (a different number)

If you need to size a column to a specific number of points or pixels, there is no direct setter; you set ColumnWidth in characters, then read .Width back and adjust in a short loop until the points match. It is fiddly by design — Excel's column width was always a character measure, and .Width was bolted on afterward as a read-only convenience.

RowHeight and wrap text

RowHeight in points is straightforward, with one behaviour worth knowing: when a cell has wrap text on, Excel grows the row height automatically to fit the wrapped lines — unless you have set an explicit RowHeight, which pins it and can clip the text:

ws.Range("B2").WrapText = True     ' let long text wrap onto multiple lines
ws.Rows(2).EntireRow.AutoFit       ' grow the row to fit the wrapped text
' Do NOT then set Rows(2).RowHeight = 15 - that pins it and clips the wrap

The rule of thumb: if you want a row to fit wrapped content, turn on WrapText and let AutoFit (or Excel's automatic growth) size it. Setting a fixed RowHeight afterward overrides that and is how wrapped text ends up cut off.

Here is the one that wastes the most time, and it links straight back to VBA Merge Cells: AutoFit ignores merged cells. A row whose only tall content is a merged, wrapped cell will not grow to fit it — AutoFit measures unmerged cells and sees nothing that needs the extra height, so the text stays clipped. This is one more reason merged cells cause trouble in code: they defeat the very command you would reach for to make them readable. If you must keep a merged wrapped block, set its RowHeight explicitly (measure the text and compute the height yourself); better, avoid the merge and use Center Across Selection, which AutoFit can see.

Hiding a column: not width zero

A tempting shortcut for hiding a column is to set its width to zero. Resist it — use the .Hidden property, covered in VBA Hide Columns:

ws.Columns("B").Hidden = True      ' the right way - reversible, obvious, restores its old width
ws.Columns("B").ColumnWidth = 0    ' the trap - "hidden" but you have lost the original width

Hidden = True remembers the column's real width and restores it when you unhide. ColumnWidth = 0 throws that width away — unhiding it later means guessing what it used to be — and a zero-width column is easy to miss when someone later wonders why data seems to be missing. Width is for sizing; use Hidden for visibility.

How ExcelMaster helps

Sizing hides more edges than it seems: two different units for width and height, an AutoFit that errors on a cell range and silently skips merged cells, a .Width you can read but not set, and a zero-width "hide" that loses the original size. Each one fails quietly — clipped text, an unexpected number, or a column that will not come back.

ExcelMaster lets you say what the layout should do. Ask it to "auto-fit columns A to C and give row 1 some breathing room," and it promotes the range to EntireColumn before calling AutoFit, sets RowHeight in points, turns on WrapText where text needs to flow, and hides columns with .Hidden rather than a zero width. You keep the workbook and the code; you skip the layout that came out clipped because AutoFit never saw the merged cell.

Frequently asked questions

How do I set column width and row height in Excel VBA?

Assign ColumnWidth and RowHeight on whole columns and rows: Columns("A").ColumnWidth = 20 and Rows(1).RowHeight = 28. Remember the units differ — ColumnWidth is in characters of the Normal font, RowHeight is in points — so the same number means different sizes for each. From a cell, use Range("A1").EntireColumn.ColumnWidth to size the whole column.

How do I AutoFit columns in VBA?

Call AutoFit on an entire column or row: Columns("A:C").AutoFit or Range("A1:C5").EntireColumn.AutoFit. Calling AutoFit on a plain cell range like Range("A1:C5").AutoFit raises error 1004, because AutoFit only works on whole columns or rows. Promote the range with .EntireColumn or .EntireRow first.

Why doesn't AutoFit work on my merged cells?

AutoFit ignores merged cells — it measures unmerged content only, so a row whose tall content sits in a merged, wrapped cell will not grow to fit it and the text stays clipped. Set the RowHeight explicitly for that row, or avoid the merge and use Center Across Selection, which AutoFit can measure.

What is the difference between ColumnWidth and Width in VBA?

ColumnWidth is settable and measured in characters of the Normal font — this is the property you assign to. .Width is read-only and measured in points; it reports the rendered width but you cannot set it. To size to a specific number of points, set ColumnWidth in characters, then read .Width back and adjust until it matches.

Should I set column width to zero to hide a column in VBA?

No. Use Columns("B").Hidden = True, which hides the column reversibly and restores its original width when you unhide it. Setting ColumnWidth = 0 discards the original width, makes the column easy to overlook, and forces you to guess its size later. Keep width for sizing and use Hidden for visibility.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-14.

Related guides: VBA Hide Columns · VBA Merge Cells · VBA Borders · VBA Range · VBA With