How to Autofit Columns Using Macros in Excel

Ever encountered a situation where the worksheet is ready, but the column widths are different for each column? You can easily make the worksheet presentable by auto-fitting columns with just one click using macros in Excel. In this article, you will learn how to autofit columns in Excel.

Key Takeaways

• Autofit adjusts each column to match its longest cell value.
• A macro can autofit all columns with one click.
• It saves time when working with large worksheets.
• The macro keeps all data fully visible without manual resizing.
• You can run the macro anytime after updating your data.

 

What is a Macro in Excel?

A macro is a series of recorded actions that Excel can execute automatically. These actions can include formatting cells, entering data, or performing calculations. Macros are written in Visual Basic for Applications (VBA), but the best part is that you don’t need to know coding to create one!

 

How to Autofit Columns Using Macros in Excel

STEP 1: Go to Developer > Code > Visual Basic

 

STEP 2: Make sure Sheet2 is selected as we want to autofit the columns there.

Paste in your code and Select Save. Close the window afterwards.

Sub AutoFitAllColumns()
Activate
Cells.Select
'See the magic happen!
Cells.EntireColumn.AutoFit
End Sub

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

 

Make sure your macro is selected. Click Run.

 

With just one click, all of the columns are automatically fitted now!

 

Common Issues

• Macros may be disabled in your workbook. Enable macros when opening the file or adjust your Trust Center settings to allow VBA macros.
• Autofit adjusts the width based on the longest visible cell value. Merged cells or wrapped text can prevent columns from resizing as expected.
• The macro works on the active worksheet. Make sure you open the correct sheet before running the macro.
• Running the macro again recalculates the column widths based on the current data. If your data changes frequently, simply run the macro again to update the widths.
• Excel does not allow column resizing on protected worksheets. Unprotect the sheet before running the macro or include unprotect and protect commands in your VBA code.

 

Frequently Asked Questions

How can I create a macro to autofit columns in Excel?
You can create a macro using VBA by opening the Visual Basic for Applications (VBA) editor (Alt + F11), inserting a new module, and entering the following code:

Sub AutofitColumns()
Cells.EntireColumn.AutoFit
End Sub

Then, run the macro to automatically adjust all column widths to fit their content.

Can I autofit only specific columns using a macro?
Yes, you can specify which columns to autofit by modifying the code. For example, to autofit only columns A to C, use:

Sub AutofitSpecificColumns()
Columns("A:C").AutoFit
End Sub

This will adjust only the selected columns.

How do I assign the autofit macro to a button for quick use?
You can insert a button by going to Developer > Insert > Button (Form Control), then right-clicking the button and selecting Assign Macro. Choose the autofit macro you created, and clicking the button will automatically adjust the column widths.

Will the autofit macro work on protected sheets?
No, the autofit macro won’t work on protected sheets unless the sheet is unprotected first. You can modify the macro to unprotect the sheet before autofitting and then protect it again:

Sub AutofitProtectedSheet()
ActiveSheet.Unprotect "yourpassword"
Cells.EntireColumn.AutoFit
ActiveSheet.Protect "yourpassword"
End Sub

Replace "yourpassword" with the actual password of the sheet.

Can I run the autofit macro automatically when I open a workbook?
Yes, you can make the macro run automatically when the workbook opens by placing it inside the Workbook_Open event. Open the VBA editor, go to ThisWorkbook, and enter:

Private Sub Workbook_Open()
Cells.EntireColumn.AutoFit
End Sub

This will autofit columns automatically whenever the workbook is opened.