VBA to Wrap Text for Entire Sheet in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Have you ever seen long lines of text that extend beyond the cell’s boundaries? or text that does not fit within the cell’s width? If your answer is yes, please don’t worry. We can use multiple VBA codes in Excel to wrap text for an entire sheet. By using VBA to wrap text for an entire sheet, one can make the dataset more readable and improve the appearance of Excel sheets.

Table of Contents Expand

How to Open the VBA Macro Editor in Excel

You need to follow the steps below to get into the VBA Macro Editor in your worksheet:

You will see the Developer tab at the top of the worksheet. Click on the Developer tab and select Visual Basic. To display the Developer tab on the ribbon, you need to customize it. Alternatively, you can press Alt+F11.

A new window will appear. It is the Visual Basic Editor. To write new code, go to Insert > Module.

In the module, write the code and click on the Run button to run the code. You can also press the F5 key to run the code.

VBA to Wrap Text for Entire Sheet in Excel: 3 Suitable Examples

In this article, we will demonstrate 3 easy ways of using VBA to wrap text for an entire sheet. Here in the dataset, we have the product catalog of a company with product names, descriptions, and prices. The product names and descriptions are mostly invisible as the text in the cells is longer than the cell width. We will use VBA to wrap the text for an entire sheet to view the text inside all cells clearly.

1. Wrap Text in Entire Sheet for Active Worksheet Using Excel VBA

We can use VBA to wrap the text for the entire active worksheet.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Active_Worksheet()
    ActiveSheet.Cells.WrapText = True
End Sub

VBA Breakdown

Sub Active_Worksheet()

The first line declares a subroutine called Active_Worksheet.

ActiveSheet.Cells.WrapText = True
End Sub

The second line sets the WrapText property of all cells in the currently active worksheet to True. This will cause any text that is too long to fit in a cell to be wrapped in the next line. Overall, this code is a simple way to adjust the formatting of the current worksheet to make it more readable and user-friendly.

2. Wrap Text for Any Worksheet Using Excel VBA

We can use VBA to wrap text on any worksheet.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Any_Worksheet()
    Dim entire_sheet As Worksheet
    Set entire_sheet = ThisWorkbook.Worksheets("Product_Catalogue")
    entire_sheet.Cells.WrapText = True
End Sub

VBA Breakdown

Sub Any_Worksheet()

The first line declares a subroutine called Any_Worksheet.

    Dim entire_sheet As Worksheet

The second line creates a variable called entire_sheet of type Worksheet.

    Set entire_sheet = ThisWorkbook.Worksheets("Product_Catalogue")

The third line assigns the worksheet object with the name Any_Worksheet from the current workbook (i.e., ThisWorkbook) to the variable entire_sheet.

    entire_sheet.Cells.WrapText = True
End Sub

The fourth line sets the WrapText property of all cells in the Any_Worksheet worksheet to True. This will cause any text that is too long to fit in a cell to be wrapped up in the next line. Overall, this code is a more flexible way to adjust the formatting of a specific worksheet in the workbook, rather than just the active worksheet.

3. Wrap Text for Multiple Worksheets at Once Using Excel VBA

We can use VBA to wrap text for multiple or all worksheets in an open workbook.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Multiple_Worksheets()
    Dim entire_sheet As Worksheet
    Dim sheet_count As Integer    
    sheet_count = ThisWorkbook.Worksheets.Count
    For i = 1 To sheet_count
        Set entire_sheet = ThisWorkbook.Worksheets(i)
        entire_sheet.Cells.WrapText = True
    Next i
End Sub

VBA Breakdown

Sub Multiple_Worksheets()

The first line declares a subroutine called Multiple_Worksheets.

    Dim entire_sheet As Worksheet
    Dim sheet_count As Integer

The second line creates a variable called entire_sheet of type Worksheet. The third line creates a variable called sheet_count of type Integer.

    sheet_count = ThisWorkbook.Worksheets.Count

The fourth line sets sheet_count to the number of worksheets in the current workbook using the Count property of the Worksheets.

    For i = 1 To sheet_count
        Set entire_sheet = ThisWorkbook.Worksheets(i)
        entire_sheet.Cells.WrapText = True
    Next i
End Sub

The fifth line begins a For loop that will iterate through each worksheet in the workbook. The loop variable i is initialized to 1, and the loop continues until i is greater than sheet_count. Then it sets the entire_sheet to the i-th worksheet in the workbook using the Worksheets property of the ThisWorkbook The code sets the WrapText property of all cells in the current worksheet to True. This will cause any text that is too long to fit in a cell to be wrapped in the next line. Overall, this code is a more flexible way to adjust the formatting of all worksheets in the workbook, rather than just the active worksheet or a specific worksheet.

Read More: Excel VBA to Wrap Text

How to Wrap Text in a Cell Using Excel VBA

We can use VBA to wrap text in a cell.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Cell_Wrap()
    Dim entire_sheet As Worksheet
    Set entire_sheet = ThisWorkbook.Worksheets("Cell")
    entire_sheet.Range("C5").WrapText = True
End Sub

VBA Breakdown

Sub Cell_Wrap()

The first line declares a subroutine called Cell_Wrap.

    Dim entire_sheet As Worksheet

The second line creates a variable called entire_sheet of type Worksheet.

    Set entire_sheet = ThisWorkbook.Worksheets("Cell")
    entire_sheet.Range("C5").WrapText = True
End Sub

The third line assigns the worksheet object with the name Cell from the current workbook (i.e., ThisWorkbook) to the variable entire_sheet. Then it sets the WrapText property of the cell with the address C5 in the Cell worksheet to True. This will cause any text that is too long to fit in the cell to be wrapped up in the next line. Overall, this code is a simple way to adjust the formatting of a specific cell in a specific worksheet in the workbook.

How to Wrap Text in a Range of Cells Using Excel VBA

We can use VBA to wrap text in a range of cells.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Range_Wrap()
    Dim entire_sheet As Worksheet
    Set entire_sheet = ThisWorkbook.Worksheets("Range_Cells")
    entire_sheet.Range("C5:C9").WrapText = True
End Sub

VBA Breakdown

Sub Range_Wrap()

The first line declares a subroutine called Range_Wrap.

    Dim entire_sheet As Worksheet

The second line creates a variable called entire_sheet of type Worksheet.

    Set entire_sheet = ThisWorkbook.Worksheets("Range_Cells")
    entire_sheet.Range("C5:C9").WrapText = True
End Sub

The third line assigns the worksheet object with the name Range_Cells from the current workbook (i.e., ThisWorkbook) to the variable entire_sheet. Then it sets the WrapText property of all cells within the range C5:C9 in the Range_Cells worksheet to True. This will cause any text that is too long to fit in a cell to be wrapped in the next line. Overall, this code is a simple way to adjust the formatting of a specific range of cells in a specific worksheet in the workbook.

How to Disable Wrap Text in Excel VBA

We can use VBA to disable wrapping text or unwrap text in Excel.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Disable_WrapText()
    Dim entire_sheet As Worksheet
    Set entire_sheet = ThisWorkbook.Worksheets("UnWrap")
    entire_sheet.Cells.WrapText = False
End Sub

VBA Breakdown

Sub Disable_WrapText()

The first line declares a subroutine called Disable_WrapText.

    Dim entire_sheet As Worksheet

The second line creates a variable called entire_sheet of type Worksheet.

    Set entire_sheet = ThisWorkbook.Worksheets("UnWrap")

The third line assigns the worksheet object with the name UnWrap from the current workbook (i.e., ThisWorkbook) to the variable entire_sheet.

    entire_sheet.Cells.WrapText = False
End Sub

The fourth line sets the WrapText property of all cells in the UnWrap worksheet to False. This will prevent any text that is too long to fit in a cell from being wrapped into the next line. Overall, this code is a simple way to disable text wrapping in all cells of a specific worksheet in the workbook.

How to Adjust Column Width in Excel Using VBA

We can use VBA to adjust column width in an Excel worksheet.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Column_Width()
    Dim entire_sheet As Worksheet
    Set entire_sheet = ThisWorkbook.Worksheets("Column_Width")
    entire_sheet.Range("B5").ColumnWidth = 25
End Sub

VBA Breakdown

Sub Column_Width()

The first line declares a subroutine called Column_Width.

    Dim entire_sheet As Worksheet

The second line creates a variable called entire_sheet of type Worksheet.

    Set entire_sheet = ThisWorkbook.Worksheets("Column_Width")

The third line assigns the worksheet object with the name Column_Width from the current workbook (i.e., ThisWorkbook) to the variable entire_sheet.

    entire_sheet.Range("B5").ColumnWidth = 25
End Sub

The fourth line sets the ColumnWidth property of the cell with the address B5 in the Column_Width worksheet to 25. This will set the width of the entire column that contains cell B5 to 25 characters. Overall, this code is a simple way to adjust the width of a specific column in a specific worksheet in the workbook.

Things to Remember

There are a few things to remember while using VBA to wrap text for the entire sheet:

Select the appropriate worksheet.
If there are multiple sheets in the workbook, name the worksheets properly to avoid any mistakes.
You may set the WrapText property to False to revert your action.

Frequently Asked Questions

How do I wrap text in a specific column?

You can modify the loop in the VBA code to loop through any range of cells, such as a column or rectangular range of cells.

Can I wrap text in a specific row or header row?

Yes, you can modify the loop in the VBA code to loop through a specific row or range of rows.

How do I wrap text in merged cells?

You can use the MergeCells property of the cell to check if a cell is part of a merged cell. If the cell is part of a merged cell, you can use the MergeArea property of the cell to wrap text in the merged cell.

Download Practice Workbook

You can download this practice workbook while going through the article.

Wrap Text for Entire Sheet.xlsm

Conclusion

In this article, we have demonstrated three easy ways of using VBA to wrap text for an entire sheet in Excel. This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments.

<< Go Back to Wrap Text | Text Formatting | Learn Excel

VBA to Wrap Text for Entire Sheet in Excel (3 Ways)

How to Open the VBA Macro Editor in Excel

VBA to Wrap Text for Entire Sheet in Excel: 3 Suitable Examples

1. Wrap Text in Entire Sheet for Active Worksheet Using Excel VBA

2. Wrap Text for Any Worksheet Using Excel VBA

3. Wrap Text for Multiple Worksheets at Once Using Excel VBA

How to Wrap Text in a Cell Using Excel VBA

How to Wrap Text in a Range of Cells Using Excel VBA

How to Disable Wrap Text in Excel VBA

How to Adjust Column Width in Excel Using VBA

Things to Remember

Frequently Asked Questions

Conclusion

Related Articles

What is ExcelDemy?

Leave a reply Cancel reply