Excel VBA Autofit Row Height with Wrap Text (5 Effective Ways)

If you are looking for ways to autofit row height with wrap text using Excel VBA, then you are in the right place. VBA codes will make this process faster than using the features of Excel for this purpose. So, let’s start our main article.

Excel VBA to Autofit Row Height with Wrap Text: 5 Ways

Here, we have the following dataset of a company containing the sales records of some products and their corresponding salesperson’s names. But you can notice that the salespersons’ names are not fitted in the cells and so with the help of some VBA codes we will use this dataset for most cases to wrap these texts with auto fixation of the row heights.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Autofit Row Height with Wrap Text Using VBA Rows Property

Here, we will try to autofit the row heights for wrapping up the texts of the SalesPerson column using the Rows property of VBA.

Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

After that, a module will be created.

Step-02:
➤ Write the following code

Sub automation_of_row_height_with_text_1()

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

End Sub

Here, Rows(“4:10”) is for the rows from 4 to 10 where our salespersons’ names are present, WrapText is True because we want to wrap the texts for the texts which are not fitted in that column width. Finally, to show the texts we will autofit the rows’ heights by using EntireRow.AutoFit property.

➤ Press F5.
After that, you will have the increased rows heights with wrapped texts for the names which didn’t fit previously in the cells of the SalesPerson column.

Method-2: Using VBA Range Property to Autofit Row Height with Wrap Text

In this section, we will use the Range property of VBA for autofitting the row heights and wrapping the salespersons’ names to show them clearly in the SalesPerson column.

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub automation_of_row_height_with_text_2()

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

End Sub

Here, Range(“C4:C10”) is for the cells from C4 to C10 where our salespersons’ names are present, WrapText is True because we want to wrap the texts for the names which are not fitted in that column width. And then, to show the texts distinctly we will autofit the rows’ heights by using EntireRow.AutoFit property.

➤ Press F5.
Then, you will get the names properly fitted and visible in the cells after autofitting rows with wrapped texts which didn’t fit previously in the cells of the SalesPerson column.

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

For making the salesperson’s names visible, we will wrap the names with the auto fixation of row heights in the SalesPerson column, and here we will use the Selection property.

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub automation_of_row_height_with_text_3()

Selection.WrapText = True
Selection.EntireRow.AutoFit

End Sub

Selection property will consider the range which we will select and then in this range WrapText = True will ensure the wrapping operation and then to show the texts understandably the rows heights will be autofitted with the help of the EntireRow.AutoFit property.

After saving the code, go back to the main sheet.
➤ Select the range, and then, go to the Developer Tab >> Macros option.

After that, the Macro dialog box will pop up.
➤ Select the Macro name “automation_of_row_height_with_text_3“, and then, press the Run option.

Immediately after you will see the changes where the Salesperson’s names have been wrapped up along with the autofitted row heights to accommodate the names properly.

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

Here, we will use the FOR-NEXT loop to execute the operation of the wrapping up texts and auto fixation of the row heights for each cell of a defined range.

Steps:
➤ Follow Step-01 of Method-1.
➤ Write 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

Here, we have declared cells as Range, then the FOR loop will execute the operation in each cell of the range “C4:C10” for fitting the texts in the given column width with an increase of row heights.

➤ Press F5.
Afterward, you will have the names properly visible in the cells after autofitting rows with wrapped texts which didn’t fit previously in the cells of the SalesPerson column.

Method-5: Autofit Row Height Wrap Text for Merged Cells

In this section, we will work with a bunch of texts for showing it properly we attempted to merge three Cells; C3, D3, and E3 but ended with no results. So, to view the full texts we will wrap the texts with automatic fixation of the row heights for these merged cells.

Steps:
➤ Follow Step-01 of Method-1.
➤ Write 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

Here, we have declared increment as Integer, and former_width, latest_width, latest_height as Double, merged_range as Range, and sht as Worksheet. Then, we have set merged_range to the range “C3:E3” of the merged cells and sht to the worksheet named new.
Here, former_width will give the previous column widths, latest_width will give the new column widths and latest_height will give the row height after auto fitting the row to accommodate the wrapped texts. Rows(“3”) are for indicating that we have our merged cells in Row 3 and CStr will convert the numbers into text strings.

➤ Press F5.
Finally, you will have all of the text strings visible after wrapping the texts in merged cells and increasing the row height automatically.

Practice Section

For doing practice by yourself we have provided a practice section like below in a sheet named Practice. Please do it by yourself.

Download Practice Workbook

Conclusion

In this article, we tried to cover the ways to autofit row height with wrap text using Excel VBA easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.