Excel VBA to Set Vertical Alignment (5 Suitable Examples)

Sometimes a user might need to change the vertical alignment of a cell or cells to enhance the appearance of a dataset. One of the ways to change the vertical alignment is to use the Excel VBA code. In this article, we will explore five different types of Excel VBA to set vertical alignment with proper illustrations.

Overview of Vertical Alignment Property in Excel VBA

Syntax:

expression.VerticalAlignment

Here, expression is a variable that represents a cell-format object.

These are some properties that you can use:

How to Launch VBA Editor in Excel

To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.

Then go to the Insert tab and click on Module to open the code Module.

Excel VBA to Set Vertical Alignment: 5 Suitable Examples

In this article, we will demonstrate how to apply five different types of Excel VBA to set vertical alignment. We will use the following dataset for this purpose.

1. Vertically Align Cell Contents to Top

First, we will align the cell contents of range B5:D12 vertically to the top. We will use the following VBA code for this purpose.

Sub VerticalAlignmentTop()
Range("B5:D12").VerticalAlignment = xlTop
End Sub

Range("B5:D12").VerticalAlignment = xlTop

2. Excel VBA to Set Vertical Alignment to Bottom

Now we will apply Excel VBA vertical alignment bottom to the range B5:D12. You can align cell contents to the bottom by using the code given below.

Sub VerticalAlignmentBottom()
Range("B5:D12").VerticalAlignment = xlBottom
End Sub

Range("B5:D12").VerticalAlignment = xlBottom

3. Excel VBA to Set Vertical Alignment to Center

You can also align cell contents vertically to the center. Write the following code in the Microsoft VBA code Module to do so.

Sub VerticalAlignmentCenter()
Range("B5:D12").VerticalAlignment = xlCenter
End Sub

Range("B5:D12").VerticalAlignment = xlCenter

Read More: How to Format Cell and Center Text with Excel VBA

4. Apply xlJustify for Vertical Alignment

The fourth type of Excel VBA to set vertical alignment is Justify vertical alignment. The text is spread out evenly from top to bottom in justified alignment. Type the following code to apply to Justify alignment to range B5:D12.

Sub VerticalAlignmentJustify()
Range("B5:D12").VerticalAlignment = xlJustify
End Sub

Range("B5:D12").VerticalAlignment = xlJustify

5. Apply Distributed Vertical Alignment

We will now apply Distributed vertical alignment to cells B5 to D12. The following code will be used for this purpose.

Sub VerticalAlignmentDistributed()
Range("B5:D12").VerticalAlignment = xlDistributed
End Sub

Range("B5:D12").VerticalAlignment = xlDistributed

Excel VBA to Align Text Vertically and Horizontally

We will apply both vertical and horizontal alignment simultaneously in this section. The VBA code to do so is described below.

Sub VerticalHorizontalAlignment()
Range("B5:D12").VerticalAlignment = xlCenter
Range("B5:D12").HorizontalAlignment = xlCenter
End Sub

Range("B5:D12").VerticalAlignment = xlCenter
Range("B5:D12").HorizontalAlignment = xlCenter

Excel VBA to Set Horizontal Alignment

There are three basic types of horizontal alignment options in Excel. You can align text on the left, in the middle, or on the right as per your need. The VBA codes for these three types of alignment are given in the next section.

i. Excel VBA to Set Horizontal Alignment in Center

Sub HorizontalAlignmentCenter()
Range("B5:D12").HorizontalAlignment = xlCenter
End Sub

Range("B5:D12").HorizontalAlignment = xlCenter

ii. Excel VBA to Set Horizontal Alignment on Left

Sub HorizontalAlignmentLeft()
Range("B5:D12").HorizontalAlignment = xlLeft
End Sub

Range("B5:D12").HorizontalAlignment = xlLeft

iii. Excel VBA Horizontal Alignment Right

Sub HorizontalAlignmentRight()
Range("B5:D12").HorizontalAlignment = xlRight
End Sub

Range("B5:D12").HorizontalAlignment = xlRight

Read More: Excel VBA Horizontal Alignment

Excel VBA to Wrap Text

The Wraptext property can easily wrap the text of any cell. It displays the contents of a cell in multiple lines. Use the following VBA code to wrap text of the range D5:D12.

Sub Wraptext()
  Range("D5:D12").Wraptext = True
End Sub

Range("D5:D12").Wraptext = True

Frequently Asked Questions

1. How do I change the default vertical alignment in Excel?

You can change the default vertical alignment in Excel from the Home tab or by using the VBA code. You can also use the Alignment tab of the Format Cells dialogue box to change vertical alignment.

2. How do I turn off wrap text in Excel VBA?

You can easily turn off wrap text in Excel VBA by declaring the Wraptext property False (Range.Wraptext = False).

Things to Remember

• You may need to turn on the wrap text option before applying vertical alignment to a cell.
• Adjust the cell width and height to display the alignment clearly.

Download Practice Workbook

Conclusion

Thanks for making it this far. I hope you find this article helpful. In this article, we demonstrated different examples of Excel VBA to set vertical alignment. We have covered the top, bottom, center, justify, and distributed alignments. Moreover, we have discussed horizontal alignment and how to apply both horizontal and vertical alignments together. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.

Related Article

• Excel VBA to Rotate Text to 90 Degrees