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:
Name | Value | Function |
---|---|---|
xlVAlignTop | -4160 | Align text at the top |
xlVAlignBottom | -4107 | Align text at the bottom |
xlVAlignCenter | -4108 | Align text at the center |
xlVAlignJustify | -4130 | Spreads out text evenly |
xlVAlignDistributed | -4117 | Aligns text with left and right margins |
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
Code Breakdown
Range("B5:D12").VerticalAlignment = xlTop
- xlTop aligns cell contents of range B5:D12 to the top.
Press F5 on your keyboard to run the code. As a result, the cells will be aligned at the top.
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
Code Breakdown
Range("B5:D12").VerticalAlignment = xlBottom
- This code aligns the cell values to the bottom
Run the code and get your desired results.
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
Code Breakdown
Range("B5:D12").VerticalAlignment = xlCenter
- This line aligns the cells to the center.
Once you run the code, you will find that the cells are now aligned to the center.
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
Code Breakdown
Range("B5:D12").VerticalAlignment = xlJustify
- xlJustify spreads out the cell contents evenly from top to bottom.
Execute the code by pressing F5 and get your desired output.
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
Code Breakdown
Range("B5:D12").VerticalAlignment = xlDistributed
- This code aligns the first word of a line with the left margin and the last word of the line with the right margin.
Finally, apply distributed vertical alignment to all the cells by running the code.
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
Code Breakdown
Range("B5:D12").VerticalAlignment = xlCenter
Range("B5:D12").HorizontalAlignment = xlCenter
- This code aligns the text in the middle horizontally and to the center vertically.
Run the code to align the text in the center vertically and in the middle horizontally together.
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
Code Breakdown
Range("B5:D12").HorizontalAlignment = xlCenter
- xlCenter aligns the text in the middle.
ii. Excel VBA to Set Horizontal Alignment on Left
Sub HorizontalAlignmentLeft()
Range("B5:D12").HorizontalAlignment = xlLeft
End Sub
Code Breakdown
Range("B5:D12").HorizontalAlignment = xlLeft
- This line aligns the cell contents to the left.
iii. Excel VBA Horizontal Alignment Right
Sub HorizontalAlignmentRight()
Range("B5:D12").HorizontalAlignment = xlRight
End Sub
Code Breakdown
Range("B5:D12").HorizontalAlignment = xlRight
- This code aligns the text to the right.
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
Code Breakdown
Range("D5:D12").Wraptext = True
- This line makes the cells D5 to D12 to wrap text within cells, allowing it to be displayed in multiple lines if necessary.
Press F5 to run the code and get the desired output.
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
Download this practice workbook to exercise while reading this article.
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 Articles
- All Types of Alignment in Excel
- How to Top Align in Excel
- How to Middle Align in Excel
- How to Bottom Align in Excel
- How to Left Align in Excel
- How to Align Right in Excel
- How to Change Alignment in Excel
- How to Center Text in a Cell in Excel
- How to Justify Text in Excel
- Align Text in Excel
- How to Apply Center Horizontal Alignment in Excel
- Align Decimal Points in Excel
- How to Align Columns in Excel
- How to Align Currency Symbol in Excel
- How to Align Colon in Excel
- Default Alignment of Numbers in Excel
- How to Align Numbers in Excel
- How to Format Cell and Center Text with Excel VBA
- Text Alignment with VBA Command Button in Excel
- [Fixed!] Excel Cell Alignment Not Working
- Excel VBA Horizontal Alignment