In this guide, we’re going to show you how to autofit rows using macros in Excel.
Autofit Method
You can use the AutoFit feature by calling the method with same name: AutoFit. The AutoFit method is defined under Range object which represents a cell, row, column, or selection of cells. To AutoFit rows using macros, you need to express the rows you want to autofit as the range object. Below are some examples.
| Worksheets("Sheet1").Rows("1:10").AutoFit | AutoFits rows through 1st to 10th in Sheet1. |
| Worksheets("Sheet1").Range("A5:A20").Rows.AutoFit | AutoFits rows through 5th to 20th in Sheet1. |
| Worksheets("Sheet1").Rows(5).AutoFit | AutoFits the row 5 in Sheet1. |
| Worksheets("Sheet1").Cells.EntireRow.AutoFit | AutoFits all the row in Sheet1. |
Each command above ends with the AutoFit method. Note that the Rows method can return a range object you need to run the AutoFit method.
How to create a macro autofit rows using macros
Let's review the basics of creating macros. To start, you need to open the VBA (Visual Basic for Applications) window and add a module. A module is where you write code.
- Press Alt + F11 to open the VBA window.
- In the VBA window, click Insert on the toolbar.
- Click the Module option.
- Create a subroutine by typing in Sub MyAutoFit and pressing Enter (MyAutoFit is just a placeholder, feel free to change it).
- Enter your code between Sub MyAutoFit and End Sub (End Sub will be created automatically upon Enter key.).
Remember to save your workbook as an XSLM (Macro enabled workbook) after entering your code. Otherwise, your code will not be saved.
