Home » Macros & Excel VBA » Excel VBA to Set Vertical Alignment (5 Suitable Examples)

Excel VBA to Set Vertical Alignment (5 Suitable Examples)

Avatar photo
Written by Alif Bin Hussain
Last updated: Sep 21, 2023
Get FREE Advanced Excel Exercises with Solutions!

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 different kinds of Excel VBA Vertical Alignment

Table of Contents hide
Overview of Vertical Alignment Property in Excel VBA
How to Launch VBA Editor in Excel
Excel VBA to Set Vertical Alignment: 5 Suitable Examples
1. Vertically Align Cell Contents to Top
2. Excel VBA to Set Vertical Alignment to Bottom
3. Excel VBA to Set Vertical Alignment to Center
4. Apply xlJustify for Vertical Alignment
5. Apply Distributed Vertical Alignment
Excel VBA to Align Text Vertically and Horizontally
Excel VBA to Set Horizontal Alignment
i. Excel VBA to Set Horizontal Alignment in Center
ii. Excel VBA to Set Horizontal Alignment on Left
iii. Excel VBA Horizontal Alignment Right
Excel VBA to Wrap Text
Frequently Asked Questions
Things to Remember
Conclusion
Related Articles

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.

Go to Microsoft Visual Basic Application

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

Insert a code Module in VBA

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.

Excel VBA Vertical Alignment

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

Vertically Align Cell Contents at Top

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.

Vertically Align Cell Contents at 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

Vertical Alignment Bottom

Code Breakdown

Range("B5:D12").VerticalAlignment = xlBottom
  • This code aligns the cell values to the bottom

Run the code and get your desired results.

Excel VBA Vertical Alignment Bottom

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

Vertical Alignment Center

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

Apply Justify Vertical Alignment

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.

Apply Justify Vertical Alignment

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

Apply Distributed Vertical Alignment

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.

Apply Distributed Vertical Alignment

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

Align Text Vertically and Horizontally

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

Horizontal Alignment Center

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

Excel VBA Horizontal Alignment Left

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

Excel VBA Horizontal Alignment Right

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

Excel VBA Wrap Text

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

Tags:

Alif Bin Hussain
Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

Affiliate Disclosure:

ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. I earn a small commission if you buy any products using my affiliate links to Amazon.

DMCA.com Protection Status

Join ExcelDemy Forum

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo