Excel VBA provides various methods to wrap text in cells, ranges, and entire worksheets. Knowing these methods can help you customize the appearance of your data and improve the readability of your spreadsheets. In this article, we will explore the different ways of using Excel VBA to Wrap Text.
The article will cover different examples for turning on wrap text: wrapping text to a cell, wrapping text to a range of cells, wrapping text to an entire worksheet, wrapping text, and autofitting columns. And 2 different methods for disabling wrap text: turning off wrap text in a cell using Excel VBA, and turning off wrap text in an entire worksheet using Excel VBA. Each method will be explained step-by-step, and sample codes will be provided to help you implement these techniques in your own VBA projects. By the end of this article, you will have a comprehensive understanding of how to wrap text in Excel VBA, allowing you to present your data in a clear and concise manner.
How to Launch Visual Basic Editor in Excel
To Launch Visual Basic Editor, we need to click on Visual Basic under the Developer tab.
Then, Insert a module to write the code. Finally, inside the module, we can write suitable codes to get our desired output. We have to repeat this process for adding each of the new Macros. If you are using VBA for the first time, you may need to enable the Developer tab inside the ribbon in Excel.
Excel VBA to Wrap Text: 3 Practical Examples
We can use the Wrap Text feature to wrap text in Excel cells. But VBA can make tasks easy and automatic. In the following sections, we will show some practical examples that will teach you to use Excel VBA to wrap text. Let’s dive into this.
1. Wrap Text to a Cell Using VBA
Let us look at an image of the data table, where the title of the data table is in the unwrapped text form.
So, now we are going to wrap text inside the title cell using the following code.
Code Syntax:
Sub SingleCellWrapText()
Cells(2, 2).WrapText = True
End SubCode Breakdown:
Sub SingleCellWrapText()- Begins the VBA sub-routine and provides it with the name “SingleCellWrapText”.
Cells(2, 2).WrapText = True- Specifies the cell to be wrapped by using the Cells method and sets the WrapText property to True. In this case, it refers to cell B2 (2nd row, 2nd column) of the active worksheet.
End Sub- Ends the sub-routine.
After writing the code inside the module, we need to run the code. And texts inside cell B2 will be wrapped.
2. Wrap Text to a Range of Cells
There could be several ways to apply the wrap text feature inside a range of cells.
2.1. Using UsedRange in Active Sheet
Let us look at an image of the data table, where no cell inside the data table is wrapped.
So, now we are going to wrap text inside the used range of cells using the following code.
Code Syntax:
Sub ActiveSheetRangeWrapText()
ActiveSheet.UsedRange.WrapText = True
End SubCode Breakdown:
Sub ActiveSheetRangeWrapText()- Begins the VBA sub-routine and provides it with the name “ActiveSheetRangeWrapText”.
UsedRange.WrapText = True- Wraps the text of all the cells within the used range of the active worksheet. It first identifies the active worksheet using the ActiveSheet property, then accesses the used range of the worksheet using the UsedRange property, and finally sets the WrapText property to True for all the cells within that range.
End Sub- Ends the sub-routine.
After writing the code inside the module, we need to run the code. Texts inside the used range of cells will be wrapped.
2.2. Using UsedRange in Specific Sheet
Here we will carry out the same task. Instead of an active sheet, we shall wrap texts of the used range of cells inside our desired worksheet.
Again, Let us look at an image of the data table, where no cell inside the data table is wrapped.
So, now we are going to wrap text inside the used range of cells that is inside our desired worksheet using the following code.
Code Syntax:
Sub NamedSheetRangeWrapText()
Worksheets("Name").UsedRange.WrapText = True
End SubCode Breakdown:
Sub NamedSheetRangeWrapText()- This line begins the VBA sub-routine and provides it with the name “NamedSheetRangeWrapText”.
Worksheets("Name").UsedRange.WrapText = True- This line wraps the text of all the cells within the used range of a specific worksheet named “Name”. It first identifies the worksheet using the Worksheets property and the name of the worksheet in quotes, then accesses the used range of the worksheet using the UsedRange property, and finally sets the WrapText property to True for all the cells within that range.
End Sub- This line ends the sub-routine.
After writing the code inside the module, we need to run the code. Texts inside the used range of cells inside the sheet named “Name” will be wrapped.
2.3. Wrapping Text in Specific Range
Previously, we wrapped text inside the used range of cells for both the active sheet and the desired sheet. Now, we are wrapping texts inside a specific range of cells.
Now, let us look at an image of the data table, where no cell inside the range B2:C4 is wrapped.
So, now we are going to wrap text inside the range B2:C4 using the following code.
Code Syntax:
Sub DeclaredRangeWrapText()
Range("B2:C4").WrapText = True
End SubCode Breakdown:
Sub DeclaredRangeWrapText()- Begins the VBA sub-routine and provides it with the name “DeclaredRangeWrapText”.
Range("B2:C4").WrapText = True- Wraps the text of all the cells within a specific range of cells. It first identifies the range of cells using the Range property and the cell range in quotes and then sets the WrapText property to True for all the cells within that range.
End Sub- Ends the sub-routine.
After writing the code inside the module, we need to run the code. Texts inside the range B2:C4 will be wrapped.
2.4. Wrapping Text in Discontinuous Range
In this case, we will be wrapping text inside a discontinuous range of cells.
Now, let us look at an image of the data table, where the cells B2, C4, C5, C6, and C7 are not wrapped.
So, now we are going to wrap text inside the discontinuous range B2 and C4:C7 using the following code.
Code Syntax:
Sub DiscontinuousRangeWrapText()
Range("B2,C4:C7").WrapText = True
End SubCode Breakdown:
Sub DiscontinuousRangeWrapText()- Begins the VBA sub-routine and provides it with the name “DiscontinuousRangeWrapText”.
Range("B2,C4:C7").WrapText = True- Wraps the text of cells within a discontinuous range of cells. It first identifies the range of cells using the Range property and specifies two separate cell ranges, “B2” and “C4:C7”, separated by a comma. The WrapText property is then set to True for all the cells within those ranges.
End Sub- Ends the sub-routine.
After writing the code inside the module, we need to run the code. Texts inside the range B2 and C4:C7 will be wrapped.
3. Wrap Text of Entire Worksheet
Until now, we have wrapped text inside a cell or range of cells that did not cover the entire worksheet. Now, is the time to wrap text inside an entire worksheet or worksheets.
3.1. Wrapping Text in Active Sheet
Let us look at an image of the data table, where the cells are not wrapped.
So, now we are going to wrap text inside ActiveSheet using the following code.
Code Syntax:
Sub EntireWorksheetWrapText()
Cells.WrapText = True
End SubCode Breakdown:
Sub EntireWorksheetWrapText()- Begins the VBA sub-routine and provides it with the name “EntireWorksheetWrapText”.
WrapText = True- Wraps the text of all cells in the active worksheet. The Cells property refers to all cells in the worksheet, and the WrapText property is set to True for all cells to wrap the text.
End Sub- Ends the sub-routine.
After writing the code inside the module, we need to run the code. And texts inside the ActiveSheet will be wrapped.
3.2. Wrapping Text in Specific Sheet
This method is different from the previous one in the sense that, here we are going to wrap text inside a specific worksheet instead of ActiveSheet.
Let us look at an image of the data table inside the worksheet named “Name”, where the cells are not wrapped.
So, now we are going to wrap text inside the sheet named “Name” using the following code.
Code Syntax:
Sub NamedWorksheetWrapText()
Worksheets("Name").Cells.WrapText = True
End SubCode Breakdown:
Sub NamedWorksheetWrapText()- Begins the VBA sub-routine and provides it with the name “NamedWorksheetWrapText”.
Worksheets("Name").Cells.WrapText = True- Wraps the text of all cells in a named worksheet named “Name”. The Worksheets property refers to all the worksheets in the workbook, and the argument “Name” specifies the name of the worksheet. The Cells property refers to all cells in the worksheet, and the WrapText property is set to True for all cells to wrap the text.
End Sub
- Ends the sub-routine.
After writing the code inside the module, we need to run the code. And texts inside the specific worksheet will be wrapped.
3.3. Wrapping Text in All Worksheets
Now we are going to wrap text inside all the worksheets present in the workbook using the following code.
Code Syntax:
Sub AllWorksheetsWrapText()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.WrapText = True
Next ws
End SubCode Breakdown:
Sub AllWorksheetsWrapText()- Indicates the start of a new VBA sub-procedure named “AllWorksheetsWrapText”.
Dim ws As Worksheet- Declares a variable “ws” as a worksheet object.
For Each ws In ActiveWorkbook.Worksheets- Initiates a loop through all the worksheets in the active workbook and assigns each worksheet to the “ws” variable in turn.
Cells.WrapText = True- Sets the WrapText property of all cells in the current worksheet (“ws”) to true, which enables text wrapping in cells.
Next ws- Signals the end of the loop.
End Sub- Marks the end of the sub-procedure.
After running the code, all the worksheets inside the workbook will be formatted as wrap text.
How to Turn Off Wrap Text in a Cell Using Excel VBA
To turn off wrap text inside a single cell, we need to write the following code inside a module.
Code Syntax:
Sub SingleCellDisableWrapText ()
Cells(2, 2).WrapText = False
End SubCode Breakdown:
Sub SingleCellDisableWrapText()- Begins the declaration of a subroutine named “SingleCellDisableWrapText”.
Cells(2, 2).WrapText = False- Accesses the cell in the second row and second column (i.e., B2) and disables the WrapText property by setting it to False.
End Sub- Marks the end of the sub-procedure.
After that, we need to run the code and the text inside cell B2 will be unwrapped.
How to Turn Off Wrap Text in Entire Worksheet Using Excel VBA
To turn off wrap text inside an entire worksheet, we need to write the following code inside a module.
Code Syntax:
Sub EntireWorksheetDisableWrapText()
Cells.WrapText = False
End SubCode Breakdown:
Sub EntireWorksheetDisableWrapText()- Begins the VBA code block and declares the name of the sub-procedure.
WrapText = False- Disables wrap the text for all cells in the active worksheet by setting the WrapText property to False.
End Sub- Marks the end of the sub-procedure.
After running the code, all the cells inside the ActiveSheet will be unwrapped.
How to Turn Off Wrap Text in Entire Workbook Using Excel VBA
To turn off wrap text inside an entire workbook that is inside all the present worksheets, we need to write the following code inside a module.
Code Syntax:
Sub AllWorksheetsDisableWrapText()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.WrapText = False
Next ws
End SubCode Breakdown:
Sub AllWorksheetsDisableWrapText()- Defines the name of the Subroutine.
Dim ws As Worksheet- Declares a variable ws as a Worksheet data type.
For Each ws In ActiveWorkbook.Worksheets- Starts a For Each loop to iterate through each worksheet in the ActiveWorkbook and assign each worksheet to the variable ws.
Cells.WrapText = False- Sets the WrapText property of all cells in the current worksheet (represented by the variable ws) to False.
Next ws- Ends the loop and moves to the next worksheet.
End Sub- Marks the end of the sub-procedure.
After that, we need to run the code. And Inside each of the worksheets of the workbook, the text will be unwrapped.
Excel VBA to Wrap Text Entire Column
Let us look into the “Name” worksheet and observe that the cells inside column C are not wrapped.
To wrap an entire column of the data table or the worksheet we need to write the following code inside a module.
Code Syntax:
Sub EntireColumnWrapText()
Range("C:C").WrapText = True
End SubCode Breakdown:
Sub EntireColumnWrapText()- Begins the declaration of a new sub procedure called “EntireColumnWrapText”.
Range("C:C").WrapText = True- Sets the WrapText property to True for all cells in column C of the currently active worksheet. It achieves this by referencing the range of cells in column C using the Range method, and then setting the WrapText property of this range to True.
End Sub- Marks the end of the sub-procedure.
After running the code, all the cells inside column C will be wrapped.
Excel VBA to Wrap Text Entire Row
Let us look into the “Name” worksheet and observe that the cells inside row 4 are not wrapped.
To wrap an entire row of the data table or the worksheet we need to write the following code inside a module.
Code Syntax:
Sub EntireRowWrapText()
Range("4:4").WrapText = True
End SubCode Breakdown:
Sub EntireRowWrapText()- Declares the name of the subroutine that will wrap the text for the entire row.
Range("4:4").WrapText = True- Select the fourth row and set the WrapText property to True, which allows text to be displayed on multiple lines within a single cell.
End Sub
- Marks the end of the sub-procedure.
After running the code, all the cells inside Row 4 will be wrapped.
Things to Remember
- If you are wrapping text to a large range of cells, consider using the autofit feature to adjust the column width automatically. This can help prevent your text from being cut off or overlapping with adjacent cells.
- It is always a good practice to test your code on a sample dataset before applying it to a large and important workbook.
Download Practice Workbook
Download this practice workbook.
Conclusion
In conclusion, Excel VBA provides various methods for wrapping text that can be used to customize the appearance and readability of your spreadsheets. By implementing the various methods covered in this article, you can easily wrap text into a single cell, a range of cells, or an entire worksheet. With the knowledge gained from this tutorial on Excel VBA to wrap text, you can take your Excel skills to the next level and create professional-looking spreadsheets. If you have any further questions or comments, be sure to leave them in the comment section below.
Related Articles
- How to Wrap Text in Merged Cells in Excel
- How to Wrap Text across Multiple Cells without Merging in Excel
- Excel Auto Fit Row Height for Wrap Text
- How to Write a Paragraph in Excel Cell
- [Solution:] Excel Wrap Text Not Working for Merged Cell
- How to Use Excel VBA to Unwrap Text
<< Go Back to Wrap Text | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
