VBA & Macro Problems

How to Get and Restore Excel Column Width Using VBA

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to understand how Excel measures column widths and wants to use a VBA script to accurately capture and restore a specific column's width.

Product: Excel
Device & OS: not provided
Scenario: Using VBA macros to dynamically manipulate, save, and restore Excel column widths during spreadsheet formatting.
Observed behavior: Excel displays column width in characters and pixels in the UI, but VBA's .Width property returns a different value (points), causing discrepancies when trying to restore the exact previous width.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet application and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to retain your VBA scripts.

Solution 1Recommended

Use the VBA ColumnWidth Property to Get and Set Widths

The ColumnWidth property directly corresponds to the character-based width shown in the Excel UI, making it the most reliable method for saving and restoring column widths.

Excel's .Width property returns the column width in points (which is read-only in some contexts and does not directly match the UI), whereas .ColumnWidth returns the width in characters. To accurately restore a width, you should always rely on .ColumnWidth.

Note that pixel conversion is not a fixed ratio because it depends heavily on your system's display settings, DPI scaling, and the default font applied to the workbook.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Save the current column width

Declare a variable and store the target column's width using a script like: Dim colWidth As Double, followed by colWidth = ActiveSheet.Range("A1").ColumnWidth.

Restore the saved width

Apply the saved variable back to the column later in your script using: ActiveSheet.Range("A1").EntireColumn.ColumnWidth = colWidth.

Understanding Pixel vs. Character Units: Because pixel width varies based on screen resolution and the default font size (e.g., Calibri 11pt), relying on the character width (.ColumnWidth) ensures consistency across different devices and users.

Seamless Macro Support

Manage VBA Macros Easily with WPS Spreadsheet

WPS Office provides robust support for VBA macros, allowing you to seamlessly execute, edit, and troubleshoot scripts like column width adjustments just as you would in Microsoft Excel.

1. Download and Install WPS Office: Download WPS Office from the official website and install the lightweight suite on your computer.
2. Open your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the column width VBA scripts.
3. Enable Macros: Click 'Enable Macros' if prompted by the security warning at the top of your worksheet to allow scripts to run.
4. Access the VBA Editor: Navigate to the Developer tab and click 'VBA Editor' to view, edit, and execute your ColumnWidth scripts.

Full compatibility with Microsoft Excel macro formats (.xlsm, .xls)Built-in VBA editor for creating and modifying custom scriptsFree and lightweight alternative for powerful data analysisFamiliar ribbon interface for a zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does VBA Range.Width return a different number than Excel's Column Width dialog?

Range.Width returns the measurement in points (where 1 point equals 1/72 of an inch) and is strictly read-only. The Excel UI, however, displays column width in characters (based on the default font zero-character width) and pixels. To match the UI character value, you must use Range.ColumnWidth.

Can I set the column width using pixels in VBA?

VBA does not have a direct ColumnWidthInPixels property. Because pixel size depends on your screen DPI and default font, you generally have to calculate a conversion ratio manually or stick to using the character-based ColumnWidth property for reliable results.

How do I autofit a column's width in VBA instead of setting a specific number?

You can automatically adjust the column width to perfectly fit its contents by using the AutoFit method. For example, add the line Columns("A:A").EntireColumn.AutoFit to your VBA script.

Will my Excel VBA macros work in WPS Office?

Yes, WPS Office Pro and certain other editions include comprehensive VBA support, meaning standard macros for tasks like resizing columns, data processing, and cell formatting will run seamlessly without requiring modification.

Excel & Data Problems