VBA & Macro Problems

How to Autofit Multiple Excel Columns Based on Cell Contents Using VBA

Khadija Khan Oct 1, 2026 869 views

Question details

The user needs a method to simultaneously update and autofit the widths of multiple adjacent columns when the contents in a specific trigger cell change.

How to Autofit Multiple Excel Columns Based on Cell Contents Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the adjustment of column widths dynamically to fit new data inputs or icons using Excel macros.
Observed behavior: By default, Excel only autofits the specific column being edited. It does not automatically update neighboring columns unless manually instructed or programmed via VBA.

Before you start

Ensure you have the Developer tab enabled in Excel and remember that your document must be saved as a Macro-Enabled Workbook (.xlsm) to run VBA code successfully.

Solution 1Recommended

Use a Worksheet Change Event VBA Script

Apply a VBA macro that automatically triggers an autofit across a specified range of columns whenever a target cell is modified.

This method uses an automated event listener in Excel. By adding code to the specific sheet, any modification to your targeted cell (like Y1) will force Excel to resize multiple surrounding columns instantly.

Open the VBA Editor

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

Access the specific Sheet code

In the Project Explorer pane on the left, double-click the specific worksheet name (e.g., Sheet1) where you want the autofit action to occur.

Insert the Worksheet_Change code

Paste the following script into the code window: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("Y1")) Is Nothing Then Columns("Y:AB").EntireColumn.AutoFit End If End Sub.

Save and test

Close the VBA editor. Type a new value in cell Y1 and press Enter. Columns Y through AB will automatically resize to fit their contents.

Automated Adjustment: This script quietly runs in the background, ensuring your spreadsheet layout is always perfectly fitted to the newest data inputs.

Solution 2

Adjust Cell Indentation for Even Visual Spacing

If exact autofit creates columns that are too narrow or tightly packed, manual cell indentation provides a balanced visual workaround.

Efficient Spreadsheet Management

Easily Manage VBA and Column Widths with WPS Spreadsheet

WPS Spreadsheet offers excellent compatibility with Excel macros and VBA scripts, allowing you to run autofit codes smoothly. You can also quickly perform manual adjustments using its user-friendly interface.

1. Open WPS Spreadsheet: Launch WPS Office and open the spreadsheet document you want to automate.
2. Enable the Developer Tab: Navigate to the top ribbon. If not visible, enable the Developer tab from the settings to access macro tools.
3. Insert Your VBA Code: Click 'Visual Basic Editor', double-click your target worksheet in the Project window, and paste the Worksheet_Change autofit macro.
4. Use Manual AutoFit Tools: Alternatively, select your columns, go to the Home tab, click on 'Rows and Columns', and select 'AutoFit Columns' for an instant fix.

Seamless compatibility with Microsoft Excel (.xlsx, .xlsm, .xls) formats and structures.Native support for writing, editing, and running VBA macros and events.Intuitive formatting options to quickly adjust column widths and cell alignments.Lightweight application that processes complex, macro-heavy spreadsheets efficiently.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't standard double-clicking autofit multiple columns?

Double-clicking a single column boundary only autofits that specific column. To autofit multiple columns without VBA, you must highlight all target columns first, then double-click any column boundary within that selection.

Can I run this autofit macro without saving as an .xlsm file?

No. Standard Excel workbook formats (.xlsx) cannot store VBA macros. You must save your file as an Excel Macro-Enabled Workbook (.xlsm) to keep the Worksheet_Change event script intact and functional.

How do I autofit all columns on the entire worksheet via VBA?

Instead of specifying a limited range like Columns("Y:AB"), you can use the command 'Cells.EntireColumn.AutoFit' within your VBA script to automatically adjust every column across the entire active sheet.

Excel & Data Problems