Pinterest Pixel

How to Autofit Columns Using Macros in Excel

Bryan
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!

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

 

How to Autofit Columns Using Macros in Excel

STEP 1: Go to Developer > Code > Visual Basic

How to Insert Multiple Columns Using Macros in Excel

 

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.

How to Autofit Columns Using Macros in Excel

 

STEP 3: Let us test it out!

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

How to Autofit Columns Using Macros in Excel

 

Make sure your macro is selected. Click Run.

How to Autofit Columns Using Macros in Excel

 

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

How to Autofit Columns Using Macros in Excel

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.

If you like this Excel tip, please share it



How to Autofit Columns Using Macros in Excel | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Use Calculator By Using Macros In Excel

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...