Excel VBA Set Column Width: Code, Units, and Common Fixes

To set a column width in Excel VBA, assign a value to the column’s ColumnWidth property, such as Worksheets("Sheet1").Columns("B").ColumnWidth = 20. The value is measured in Excel’s character-based column-width units, not pixels or points; use AutoFit instead when the width should match the cell contents.

Set a Fixed Width with VBA

For a reliable macro, refer to the worksheet explicitly rather than relying on whichever sheet happens to be active. This example sets column B on the worksheet named Sheet1 to a width of 20:

Sub SetColumnWidth()
    Dim ws As Worksheet

    ' Use the worksheet in the workbook that contains this macro.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Columns("B").ColumnWidth = 20
End Sub

Change the sheet name, column letter, or number to suit the workbook. For example, setting the number to 12 makes the column narrower than setting it to 20. The property accepts a numeric value, so a value such as 14.5 is also valid. Excel may adjust or round a requested width to the increments supported by its display grid.

ThisWorkbook means the workbook containing the VBA code. That is often safer than ActiveWorkbook, which means the workbook currently in front and could be a different file. If the target sheet is in another workbook, first set a workbook variable to that workbook and then refer to its worksheet.

Set Several Columns at Once

You can set a continuous range of columns to the same width by passing a range such as B through D to Columns. The following macro makes all three columns 18 units wide:

Sub SetSeveralColumnWidths()
    Dim ws As Worksheet

    ' Set the width of columns B, C, and D together.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Columns("B:D").ColumnWidth = 18
End Sub

To apply one width to every column on a sheet, use ws.Columns.ColumnWidth = 15. This changes the entire worksheet, including currently empty columns, so it is usually better to limit the change to the columns your data uses. You can also write ws.Range("B:D").ColumnWidth = 18; using Columns makes the intent particularly clear.

When columns need different widths, assign each separately or loop through a defined set. For example, column A might hold names and need more room than a short status field in column C. Separate assignments are straightforward and easier to maintain when the layout is fixed.

How ColumnWidth Units Work

ColumnWidth is not a measurement in inches, pixels, or points. It is based on the character width of the Normal style’s standard font in the workbook. As a result, a width of 20 means roughly 20 character units, not a guarantee that every cell can display exactly 20 characters: letters have different widths, and the displayed result also depends on the font and Excel’s sizing rules.

Excel’s familiar default width is often 8.43, but the pixel width associated with that value can vary with font, display scaling, and workbook settings. If you need to set a column to a precise physical measurement, ColumnWidth is not a direct points or pixel control. The range’s Width property reports a physical width in points, but it is read-only; it is not a substitute you can assign a desired point value to.

Column width also affects what users can see, not the underlying cell value. Making a column wider does not change a number, date, or formula. It can reveal content that was previously hidden or reduce the appearance of a row of number signs (often displayed for a number or date that does not fit). It does not, however, change the cell’s number format or make long text wrap automatically.

Use AutoFit for Content-Based Width

When the goal is to make a column wide enough for its contents, use AutoFit rather than guessing a number. This example sizes column B to fit its entries:

Sub AutoFitOneColumn()
    Dim ws As Worksheet

    ' Adjust column B to fit the cells in that column.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Columns("B").AutoFit
End Sub

To size a group of columns, use ws.Columns("A:D").AutoFit. AutoFit considers the content in the specified columns and adjusts their widths accordingly. This is useful after a macro imports data, writes labels, or refreshes a report.

AutoFit can produce unexpectedly wide columns when a cell contains a long description, a long unbroken string, or an identifier. In that case, a fixed width may make the sheet easier to scan. You can combine both approaches: AutoFit the used columns first, then set a maximum-like practical width for any column that should not dominate the page. VBA does not provide a single AutoFit-with-maximum-width command, so a macro that needs such a rule must compare the resulting width and apply a chosen limit.

Merged cells are a common exception: AutoFit does not reliably size a column to fit text in a merged range. If a layout depends on merged cells, consider unmerging the cells, choosing a fixed width, or adjusting row height and text wrapping as appropriate.

Choose a Fixed Width or AutoFit

The right method depends on whether the sheet has a stable layout or changing content. Fixed widths preserve a consistent report shape; AutoFit responds to the actual values. Consider these practical factors:

Use a fixed width for templates, forms, dashboards, or recurring exports where columns should line up consistently from run to run.
Use AutoFit for newly imported data or columns whose contents vary and should remain readable without manually estimating a width.
Use a combination when most fields can fit their contents but one or two descriptive columns need a deliberate limit for printing or screen viewing.
Check the longest values before applying AutoFit to names, notes, URLs, or IDs; one unusual value can make the whole column inconveniently broad.

For printed reports, a width that looks comfortable on screen may still make the printout span extra pages. Set the widths with the intended page layout in mind, then check Print Preview. Column width and page scaling are related visually, but setting ColumnWidth does not itself choose a print scaling percentage.

Run the Macro and Save It Correctly

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure into the module, and run it with the cursor inside the procedure by pressing F5. You can also return to Excel and run a saved macro from Developer > Macros. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.

On Mac, VBA is available in the desktop Excel application, but shortcuts and menus can differ with keyboard settings and Excel release. The Visual Basic Editor is commonly opened with Option+F11; on some keyboards you may also need the Fn key. Excel for the web does not run VBA macros, so use the Windows or Mac desktop application to execute this code.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want the macro to remain in the file. Excel may show a security warning when a workbook contains macros; only enable macros in files from sources you trust. Save a copy of the workbook before running a macro that changes a worksheet. Macro actions cannot be undone with Ctrl+Z on Windows (or Command+Z on Mac), so a backup is the safest way to preserve the original layout.

Common Problems and How to Fix Them

Run-time error for a missing sheet: Check the worksheet tab name, including spaces and punctuation. Worksheets("Sheet1") must match an existing sheet in the workbook referenced by the code.
The wrong sheet changes: Avoid unqualified code such as Columns("B").ColumnWidth = 20, which acts on the active sheet. Qualify it with the intended worksheet, for example ws.Columns("B").ColumnWidth = 20.
The column still looks too narrow: Confirm that the code targets the right column and worksheet, then check the font and display scaling. A character-based width is not a fixed pixel measurement.
AutoFit makes a column enormous: Look for an unusually long cell value, such as a URL or note. Use a fixed width for that field, or handle the long content through wrapping or a different layout.
The text remains cut off: Widening the column may not solve every display issue. Check whether the cell is merged, whether text wrapping is enabled, and whether an adjacent cell contains content that prevents text from spilling across it.

Another frequent source of confusion is trying to undo a macro with Ctrl+Z. Excel clears its normal undo history when VBA runs, so the change may not be reversible through the standard Undo command. A saved copy is more dependable than expecting a macro change to appear in the undo list.

Frequently Asked Questions

What is the VBA code to set column A to width 25?
With the target worksheet named Sheet1, use ThisWorkbook.Worksheets("Sheet1").Columns("A").ColumnWidth = 25. In a complete macro, declare and set a worksheet variable as shown above, then use its Columns("A") reference.

How do I set all columns to the same width?
Use a worksheet-qualified statement such as ws.Columns.ColumnWidth = 15, after setting ws to the intended worksheet. This affects every column on that sheet, not only columns containing data.

How do I make a column fit its contents in VBA?
Use ws.Columns("B").AutoFit for one column or ws.Columns("A:D").AutoFit for a group. Merged cells and very long values can make the result unsuitable, so inspect the adjusted layout.

Is ColumnWidth measured in pixels?
No. It uses character-based units related to the workbook’s Normal style font. Pixel or point equivalents can vary, and the Width property reports points but cannot be assigned a target width.

Can I run this VBA in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not execute VBA. Open the file in desktop Excel for Windows or Mac to run the macro.

When to Use VBA Instead of Manual Resizing

For a one-time adjustment, dragging a column boundary or using Home > Format > AutoFit Column Width may be quicker than writing a macro. VBA is more useful when the same layout must be applied repeatedly, when a workbook is generated or refreshed automatically, or when several sheets need consistent widths. A small, explicit procedure is generally easier to check than a macro that changes every column without a clear reason.

Keep the target sheet and columns visible in the code, choose fixed widths for stable layouts and AutoFit for content-driven ones, and test the result on a copy before incorporating the macro into a routine. Those choices make column sizing predictable while avoiding accidental changes to unrelated parts of a workbook.

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.