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.
Table of Contents
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.
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.