How to Autofit Columns Using Macros in Excel

My column widths are all over the place and I want to make it look more presentable. Did you know you can autofit columns using Macros in Excel? And with this cool trick, it can be done in just one click! Make sure your Excel has the Developer Tab enabled following this tutorial. I explain how you can do this below step by step!

Key Takeaways

• Automates Column Resizing: Using VBA macros, you can automatically adjust column widths to fit the content, ensuring that all data is visible without manually resizing each column.
• Works for Specific or All Columns: The macro can be applied to a specific column (Columns("A").AutoFit), a range of columns (Columns("A:C").AutoFit), or the entire worksheet (Cells.EntireColumn.AutoFit).
• Enhances Readability and Formatting: Autofitting columns prevents text from being cut off or overflowing into adjacent cells, improving the overall appearance and readability of your worksheet.
• Easily Triggered with a Button or Shortcut: The macro can be assigned to a button or keyboard shortcut, making it a quick and convenient solution for formatting data on demand.
• Efficient for Large Datasets: When dealing with extensive data, macros help save time by instantly adjusting column widths instead of manually resizing them, making spreadsheet management more efficient.

What does it do?

Autofit all columns to fit to its contents

Copy Source Code:

Sub AutoFitAllColumns()
Activate
Cells.Select

'See the magic happen!
Cells.EntireColumn.AutoFit
End Sub

Final Result: 

 

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.

 

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!

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.