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

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel VBA autofit row height wrap text

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.

Excel VBA autofit row height wrap text

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

Rows property

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

Rows property

After that, a module will be created.

Excel VBA autofit row height wrap text

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.

Rows 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.

Excel VBA autofit row height wrap text


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.

Excel VBA autofit row height wrap text

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.

Range 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.

Range property


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.

Excel VBA autofit row height wrap text

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.

Selection property

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

Selection property

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

Excel VBA autofit row height wrap text

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.

Selection property


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.

Excel VBA autofit row height wrap text

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.

FOR-NEXT Loop

➤ 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.

FOR-NEXT Loop


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.

Excel VBA autofit row height wrap text

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.

merged cells

➤ 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.

merged cells


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.

practice


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo