VBA & Macro Problems

How to Unwrap Text and Autofit Excel Columns Using a VBA Macro

Muhammad TalhaMuhammad Talha Nov 2, 2026 870 views

Question details

The user needs a VBA macro to automatically remove text wrapping and adjust the width of all used columns in an Excel worksheet to fit their content.

How to Unwrap Text and Autofit All Columns Using VBA in Excel
Product
Excel
Device & OS
not provided
Scenario
Formatting a messy dataset where text is wrapped inconsistently and column widths do not match the length of the data.
Observed behavior
The text wrapping needs to be disabled and columns autofitted across the entire used range automatically using a single macro execution.
Before you start

Ensure you have saved your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code, and confirm that the Developer tab is enabled in your Excel ribbon.

Solution 1Recommended

Use a VBA Macro to Format the Active Sheet

This is the most efficient method to instantly disable text wrapping and autofit all columns in the currently active worksheet.

By utilizing the ActiveSheet.UsedRange property, this VBA macro applies formatting only to the cells that contain data. This prevents Excel from unnecessarily processing empty columns, ensuring the macro runs quickly even on larger datasets.

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.

2
Insert a New Module

In the top menu, click on 'Insert' and select 'Module'. This will create a blank canvas for your macro code.

3
Paste the VBA Code

Copy and paste the following code into the module window: Sub UnwrapAndAutofitColumns() Dim targetRange As Range Set targetRange = ActiveSheet.UsedRange With targetRange .WrapText = False .EntireColumn.AutoFit End With End Sub

4
Run the Macro

Close the VBA Editor. In your Excel workbook, press ALT + F8 to open the Macro dialog, select 'UnwrapAndAutofitColumns', and click 'Run'.

Use a VBA Macro to Format the Active Sheet
Handling Excessively Wide Columns: If a cell contains a very long paragraph, AutoFit might make the column excessively wide. To fix this, you can modify the macro to loop through columns and set a maximum ColumnWidth if it exceeds your desired limit.
Efficient Macro Execution in WPS Office

Format Spreadsheets Automatically with WPS Office

WPS Spreadsheet provides excellent compatibility with Excel VBA macros. You can run formatting macros like unwrapping text and autofitting columns seamlessly within its fast, user-friendly interface.

  1. 1. Enable the Developer Tab: Open WPS Spreadsheet, go to the top menu, and select 'Developer' to access macro tools.
  2. 2. Open the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to launch the macro editor.
  3. 3. Insert Code and Run: Insert a module, paste the UnwrapAndAutofitColumns code, and run it directly to format your active spreadsheet.
Seamless support for Excel VBA macros and scriptsFully compatible with Microsoft Excel formats (.xlsx, .xlsm)Lightweight architecture runs efficiently without freezing on large datasetsFree to download with a familiar ribbon interface
microsoft office alternative - wps office

Frequently Asked Questions

How can I apply this macro to a specific range of cells instead of the whole sheet?

You can modify the code by changing 'Set targetRange = ActiveSheet.UsedRange' to specify your exact range, for example: 'Set targetRange = ActiveSheet.Range("A1:D50")'.

Why is the AutoFit macro not working on certain columns?

AutoFit generally struggles with merged cells. If your worksheet contains merged cells across columns, the AutoFit function in VBA might fail to resize them correctly. Unmerge the cells first, run the macro, and then format accordingly.

Can I make this macro run automatically across all sheets in my workbook?

Yes, you can enclose the code in a 'For Each ws In ThisWorkbook.Worksheets' loop. Inside the loop, change 'ActiveSheet.UsedRange' to 'ws.UsedRange' to apply the formatting to every tab.