How to Unwrap Text and Autofit Excel Columns Using a VBA Macro
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.

- 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.
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.
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.
Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
In the top menu, click on 'Insert' and select 'Module'. This will create a blank canvas for your macro 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
Close the VBA Editor. In your Excel workbook, press ALT + F8 to open the Macro dialog, select 'UnwrapAndAutofitColumns', and click 'Run'.

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. Enable the Developer Tab: Open WPS Spreadsheet, go to the top menu, and select 'Developer' to access macro tools.
- 2. Open the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to launch the macro editor.
- 3. Insert Code and Run: Insert a module, paste the UnwrapAndAutofitColumns code, and run it directly to format your active spreadsheet.

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.




