VBA Wrap Text (Cell, Range, and Entire Worksheet)

In VBA, there is a property called “WrapText” that you can access to apply wrap text to a cell or a range of cells. You need to write code to turn it ON or OFF. It’s a read and writes property, so you can apply it, or you can also get it if it’s applied on a cell.

In this tutorial, we will look at different ways of applying wrap text using a VBA code.

Wrap Text to a Cell using VBA

Use the following steps to apply Wrap Text using a VBA Code.

1. Define the cell where you want to apply the wrap text using the range property.
2. Type a dot to see the list of the properties and methods for that cell.
3. Select the “WrapText” property from the list.
4. Enter the equals sign “=” and type TRUE to turn on the wrap text.

Sub vba_wrap_text()
  Range("A1").WrapText = True
End Sub

You can also specify a cell using the following way.

Cells(1, 1).WrapText = True

Wrap Text to a Range of Cells

And if you want to apply wrap text to an entire range then you need to specify the range instead of a single cell.

Range("A1:A5").WrapText = True

You can also apply it to the used range (selection of the worksheet where you have entered data) by using the “UsedRange” property.

Worksheets("Sheet1").UsedRange.WrapText = True
ActiveSheet.UsedRange.WrapText = True

In the above code’s first line, you have specified the worksheet and then the “UsedProperty” to wrap the text. In the second line, you have the active sheet’s used range. But both lines work in the same way.

Here are a few more examples that you need to know:

• Non-continues cells
• Entire Row
• Entire Column
• Named Range

Range("A1:A10,C1:C10").WrapText = True
Range("A:A").WrapText = True
Range("1:1").WrapText = True
Range("myRange").WrapText = True

Wrap Text to the Entire Worksheet

To refer to the entire worksheet you need to use the Cells property as you have in the following code.

Cells.WrapText = True
Worksheets("Sheet1").Cells.WrapText = True

The first line of code refers to the active sheet and the second line to the worksheet “Sheet1”. You can also use a loop using FOR EACH (For Next) to loop through all the worksheets of the workbook and apply the wrap text on all the cells.

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
   Cells.WrapText = True
Next ws

In the above code, you have “ws” as a variable and then For Each Loop loops through all the worksheets from the workbook and applies to wrap text to the entire worksheet using Cells.

Turn OFF WrapText

As you have seen you need to turn on the WrapText property, and in the same way you can turn it off by using add declaring FALSE.

Range("A1").WrapText = False

Note: There’s one thing you need to understand if you have a value in a cell and the width of the cells is enough to store that value, then even if you apply wrap text, Excel won’t shift the content to the next line.

Toggling Text Wrap Based on Condition

You can toggle the wrapping of text based on a specific condition. For instance, you can only wrap text if a cell’s content exceeds a certain length.

Here’s an example that wraps text in cell A1 if the text length is greater than ten characters:

Sub Condition_Wrap_Text()
    With ActiveSheet.Range("A1")
        If Len(.Value) > 10 Then
            .WrapText = True
        Else
            .WrapText = False
        End If
    End With
End Sub

Here’s a breakdown of the code:

• With ActiveSheet.Range(“A1”): This line indicates that the following operations will be performed on cell A1 of the active sheet.
• If Len(.Value) > 10 Then: This line checks the text length in cell A1. If the length of the text is more than ten characters, it will execute the next line of code.
• .WrapText = True: If the text length in cell A1 is greater than 10, this line sets the wrap text property of cell A1 to true. That means the cell will automatically adjust its height to display all the text if it exceeds the width of the column.
• Else.WrapText = False: If the text length in cell A1 is 10 or fewer characters, this line sets the wrap text property of cell A1 to false. That means the cell will not adjust its height to display all the text if it exceeds the width of the column.
• End If: This line ends the If statement.
• End With: This line ends the With statement.
• End Sub: This line ends the subroutine.