How to Autofit Row Height with Wrap Text Using Excel VBA (5 Methods)

Scenario

In this tutorial, we’ll address the issue of the names of the salesperson not fitting in cells in an Excel dataset. By using VBA (Visual Basic for Applications) code, we’ll dynamically adjust row heights and wrap text to ensure clear visibility.

Method 1 – Autofit Row Height Using VBA Rows Property

Open the Visual Basic Editor:
- Go to the Developer tab and click on Visual Basic.

- The Visual Basic Editor window will open.

Create a Module:
- In the Visual Basic Editor, go to the Insert tab and select Module.

- Enter the following code

Sub automation_of_row_height_with_text_1()

Rows("4:10").WrapText = True
Rows("4:10").EntireRow.AutoFit

End Sub

- Press F5 to execute the code.
- This will adjust row heights for the salesperson names in cells C4 to C10.

Method 2 – Autofit Row Height Using VBA Range Property

Create Another Module:
- Follow the same steps as in Method 1 to create a new module.
Enter the Code:
- Enter the following code:

Sub automation_of_row_height_with_text_2()

Range("C4:C10").WrapText = True
Range("C4:C10").EntireRow.AutoFit

End Sub

This code wraps the text in cells C4 to C10 and adjusts the row heights accordingly.

Execute the Code:
- Press F5 to run the code.
- Now the salesperson names will be properly fitted and visible in the SalesPerson column.

Method 3 – Using Selection Property to Autofit Row Height with Wrap Text

To make the salesperson names visible, we’ll wrap the text and automatically adjust row heights in the SalesPerson column using the Selection property.

Steps:

Open the Visual Basic Editor:
- Go to the Developer tab and click on Visual Basic.
- The Visual Basic Editor window will open.
Enter the Code:
- Enter the following code:

Sub automation_of_row_height_with_text_3()

Selection.WrapText = True
Selection.EntireRow.AutoFit

End Sub

The Selection property considers the range you select. With WrapText = True, it wraps the text, and EntireRow.AutoFit adjusts the row heights for clear visibility.

Execute the Code:
- Save the code and return to the main sheet.
- Select the desired range.
- Go to the Developer tab and select the Macros option.

- In the Macro dialog box, choose the macro name AutofitRowHeight3 and press Run.

You’ll see the Salesperson names wrapped and the row heights adjusted to accommodate the names adequately.

Method 4 – Using FOR-NEXT Loop to Autofit Row Height and Wrap Text

Steps:

Follow Step 1 of Method 1.

Enter the Code:
- Enter the following code:

Sub automation_of_row_height_with_text_4()

Dim cells As Range

For Each cells In Range("C4:C10")
cells.WrapText = True
cells.EntireRow.AutoFit
Next cells

End Sub

- - This loop iterates through each cell in the range C4:C10, wrapping text and adjusting row heights.

Execute the Code:
- Press F5 to run the code.
- The names will be visible after autofitting rows with wrapped text in the SalesPerson column.

Method 5 – Autofit Row Height Wrap Text for Merged Cells

Follow Step 1 of Method 1.
Enter the Code:
- Enter the following code:

Sub automation_of_row_height_with_text_5()

Dim increment As Integer
Dim former_width, latest_width, latest_height As Double
Dim merged_range As Range
Dim sht As Worksheet
Set sht = Worksheets("new")
Set merged_range = Range("C3:E3")

former_width = 0
For increment = 1 To merged_range.Columns.Count
former_width = former_width + sht.cells(1, _
merged_range.Column + increment - 1).ColumnWidth
Next increment
former_width = sht.cells(1, merged_range.Column).ColumnWidth _
+ sht.cells(1, merged_range.Column + 1).ColumnWidth
merged_range.MergeCells = False
latest_width = Len(sht.cells(merged_range.Row, merged_range.Column).Value)
sht.Rows("3").EntireRow.AutoFit
latest_height = sht.Rows("3").RowHeight / merged_range.Rows.Count
sht.Rows(CStr(merged_range.Row) & ":" & CStr(merged_range.Row _
+ merged_range.Rows.Count - 1)).RowHeight = latest_height
merged_range.MergeCells = True
merged_range.WrapText = True

End Sub