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.
- Define the cell where you want to apply the wrap text using the range property.
- Type a dot to see the list of the properties and methods for that cell.
- Select the “WrapText” property from the list.
- Enter the equals sign “=” and the type TRUE to turn the wrap text ON.
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 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 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 that 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.
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel
If you are preparing for an interview, make sure to check out these VBA interview questions.