VBA & Macro Problems

How to Automatically AutoFit Excel Columns After Every Edit

Olivia Miller Sep 28, 2026 870 views

Question details

The user wants Excel to permanently and automatically adjust column widths to fit the text size immediately after entering new or longer text.

How to Automatically AutoFit Excel Columns After Every Edit

Product: Excel
Device & OS: not provided
Scenario: Entering varying lengths of text into spreadsheet cells and wanting to eliminate the need for manual column resizing.
Observed behavior: Excel does not have a built-in toggle for dynamic autofitting; text either spills over or gets truncated until the user manually triggers the AutoFit Column Width command.

Before you start

Because Excel lacks a native automatic resizing toggle, this solution requires adding a short VBA macro. Keep in mind that running VBA macros clears the Undo (Ctrl+Z) history in Excel, meaning you won't be able to undo typing actions on sheets where this macro is active.

Solution 1Recommended

Use a Worksheet_Change VBA Macro

Apply a simple background VBA script to your worksheet so that any edited column automatically adjusts its width.

This method utilizes Excel's Worksheet_Change event. It monitors the sheet for any cell edits and instantly triggers an AutoFit command on the affected column.

Open the VBA Editor

Right-click the specific sheet tab at the bottom of your Excel window (e.g., 'Sheet1') and select 'View Code' from the context menu.

Paste the Macro Code

In the blank code window that appears, paste the following code: Private Sub Worksheet_Change(ByVal Target As Range) Dim col As Range For Each col In Target.Columns col.EntireColumn.AutoFit Next col End Sub

Test the AutoFit Functionality

Close the VBA Editor window to return to your spreadsheet. Type a long string of text into any cell within that sheet and press Enter. The column will automatically widen to fit the text.

Save as a Macro-Enabled Workbook

To keep the script active for future sessions, go to File > Save As, and change the 'Save as type' dropdown to 'Excel Macro-Enabled Workbook (*.xlsm)'.

Undo History Cleared: Be aware that executing VBA code inherently flushes Excel's Undo memory. You will not be able to use the Undo function for edits made on a sheet running this macro.

Solution 2

Use the Built-in Manual AutoFit Option

If preserving your Undo history is critical, you can manually trigger the AutoFit command instead of using a macro.

Run Macros and Automate Tasks Easily in WPS Office

WPS Office fully supports VBA macros, allowing you to use AutoFit scripts seamlessly while providing a faster and more lightweight spreadsheet experience. Enjoy full compatibility with your existing Excel files.

1. Open Your Workbook in WPS: Launch WPS Spreadsheets and open your existing spreadsheet document.
2. Access the VBA Editor: Navigate to the 'Tools' tab on the ribbon and click on 'VBA Editor' to open the macro environment.
3. Apply and Save Your Macro: Paste the AutoFit macro code into the desired worksheet module, then easily save your file as an .xlsm macro-enabled workbook directly within WPS.

Full native support for VBA macros and automated scripts100% compatibility with Microsoft Excel .xlsx and .xlsm formatsLightweight design that opens heavy data files in secondsFamiliar ribbon interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Is there an Excel setting to automatically adjust column width without VBA?

No, Excel does not feature a built-in toggle or setting to automatically resize columns dynamically as you type. You must either use a VBA macro or manually select the columns and click 'AutoFit Column Width'.

How can I apply this AutoFit macro to the entire workbook?

Instead of right-clicking a single sheet and choosing 'View Code', open the VBA Editor, double-click 'ThisWorkbook' in the Project Explorer panel, and use the 'Workbook_SheetChange' event instead of 'Worksheet_Change'. This will apply the AutoFit logic across all sheets.

Why does my Undo (Ctrl+Z) stop working after I add this code?

This is a known limitation of Microsoft Excel. Running any VBA code automatically clears the Undo stack. Since this macro runs continuously in the background every time a cell is edited, it wipes the Undo history after every single edit.

Excel & Data Problems