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

In this guide, we’ll address the issue of salesperson names not fitting within 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.

Note: We’re using Microsoft Excel 365 in this example, but you can adapt these methods to other Excel versions as needed.

Excel VBA autofit row height wrap text


Method 1 – Autofit Row Height Using VBA Rows Property:

Excel VBA autofit row height wrap text

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

Rows property

    • The Visual Basic Editor window will open.
  • Create a Module:
    • In the Visual Basic Editor, go to the Insert tab and select Module.

Rows property

 

Excel VBA autofit row height wrap text

    • Enter the following code
Sub automation_of_row_height_with_text_1()

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

End Sub

Rows property

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

Excel VBA autofit row height wrap text


Method 2 – Autofit Row Height Using VBA Range Property:

Excel VBA autofit row height wrap text

  • Create Another Module:
    • Follow the same steps as in Method 1 to create a new module.
  • Write 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 row heights accordingly.

Range property

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

Range property

Remember to adapt the cell ranges and adjust the code as needed for your specific dataset. These methods ensure that text is wrapped and row heights are adjusted dynamically.


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.

Excel VBA autofit row height wrap text

Steps:

  • Open the Visual Basic Editor:
    • Go to the Developer tab and click on Visual Basic.
    • The Visual Basic Editor window will open.
  • Write 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 row heights for clear visibility.

Selection property

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

Selection property

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

Excel VBA autofit row height wrap text

  • You’ll see the Salesperson names wrapped and row heights adjusted to accommodate them properly.

Selection property


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

Excel VBA autofit row height wrap text

Steps:

  • Write 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.

FOR-NEXT Loop

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

FOR-NEXT Loop


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

Excel VBA autofit row height wrap text

  • Follow Step-01 of Method-1.
  • Write 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
      • This code adjusts row heights and wraps text for merged cells in the range C3:E3.

merged cells

  • Execute the Code:
    • Press F5 to run the code.
    • The text strings will be visible after wrapping and increasing row heights for merged cells.

merged cells


Practice Section

A practice section has been provided, as shown below in a sheet named Practice.

practice


Download Practice Workbook

You can download the practice workbook from here:


 

Get FREE Advanced Excel Exercises with Solutions!
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