How to Autofit Rows Using Macros in Excel

If your worksheet has rows with different heights, it may look professional. Some row heights may be too short, making the text not visible, whereas others take up more space than required. In this article, you will learn how to autofit rows using macros in Excel.

Key Takeaways

• Macros help automate repetitive tasks in Excel.
• VBA code can autofit all rows in one click.
• Autofit adjusts row height to display the full content.
• Merged cells may cause problems when autofitting rows.
• Save the workbook as an .xlsm file to keep the macro.

 

Introduction to Macros

What is a Macro?

A macro is an automation tool in Excel. It records or uses VBA code to perform repetitive tasks quickly. For example, if I regularly need to adjust row heights, format headers, remove blank rows, or prepare a report, I can use a macro instead of repeating the same steps manually.

Most Excel macros are written using VBA (Visual Basic for Applications).

VBA Code to Autofit Rows

Autofit all rows to fit to its contents

Copy Source Code:

Sub AutoFitAllRows()
Cells.Select

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

Final Result: 

 

STEP 1: Go to Developer > Code > Visual Basic

 

STEP 2: Make sure Sheet2 is selected as we want to autofit the rows 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 rows are automatically fitted now!

 

 

Common Problems When Using AutoFit Rows

• If the cells are merged, Excel may not be able to autofit rows properly. If possible, avoid merging the cells and use Center Across Selection for headings instead.
• If some text is not visible even after running the macro, you should use Wrap Text.
• If the macro is saved in an .xlsx file, it will not run. Make sure to save the file as an Excel Macro-Enabled Workbook (.xlsm).

 

FAQs

1. What does AutoFit Rows do in Excel?

AutoFit automatically adjusts the row height to show all cell content.

2. Can I use a macro to autofit all rows?

Yes, a simple VBA macro can autofit all rows in one click.

3. Why is AutoFit not working with merged cells?

Excel may not correctly adjust row height when cells are merged.

4. Do I need to enable Wrap Text before using AutoFit?

Use Wrap Text if long text is still not fully visible.

5. Which file format should I use to save a macro?

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).