Excel VBA, Horizontal Alignment
In this article I will explain the different horizontal alignment formattings applicable to cells and range. I have also provided the VBA code required to apply them.
For examples using horizontal alignment in VBA code please see:
- Excel VBA, Set Horizontal Alignment, (Sample Code)
- Excel VBA, Get Horizontal Alignment (Sample Code)
Jump To:
- Left (Indent), xlLeft
- Center, xlCenter
- Right (Indent), xlRight
- Fill, xlFill
- Justify, xlJustify
- Center Across Selection, xlCenterAcrossSelection
- Distributed (Indent), xlDistributed
- General, xlGeneral
- Indent, IndentLevel
Contents
Left (Indent), xlLeft:
The following code will left align the text in cell “A1”:
Range("A1").HorizontalAlignment = xlLeft
Result:
Center, xlCenter:
The following code will apply the horizontal center alignment to the text in cell “A1”:
Range("A1").HorizontalAlignment = xlCenter
Result:
Right (Indent), xlRight:
The following code will right align the text in cell “A1”:
Range("A1").HorizontalAlignment = xlRight
Result:
Fill, xlFill:
The following code will fill the cell in “A1” with the text in it:
Range("A1").HorizontalAlignment = xlFill
Result:
Justify, xlJustify:
The following code will apply the horizontal justify formatting to the text in cell “A1”. Note that the justify property will only be apparent when you have multiple lines of text in a cell and the wrap property is on:
Range("A1").HorizontalAlignment = xlJustify
Result:
Center Across Selection, xlCenterAcrossSelection:
The following code centers the text in cell “A1” among the cells A1~I1. This is a good way of centering a text over multiple columns without merging the cells:
Range("A1:M1").Select
Selection.HorizontalAlignment = xlCenterAcrossSelection
Before:
After:
Distributed, xlDistributed:
The following command applies the distributed (indent) formatting to cell “A1”. This formatting creates spaces between the words so that the entire horizontal spacing in that cell is filled:
Range("A1").HorizontalAlignment = xlDistributed
Before:
After:
General, xlGeneral:
The following command applies the general horizontal formatting to cell “A1”. This formatting causes text to be left aligned and numbers to be right aligned:
Range("A1").HorizontalAlignment = xlGeneral
Result:
Indent, IndentLevel:
The 3 formattings left aligned, right aligned and distributed accept an indentation level. The code below applies an indentation to the cells A1, A2 and A3:
Range("A1").IndentLevel= 1
Before:
- Excel VBA Formatting Cells and Ranges Using the Macro Recorder
- VBA Excel, Alignment
- Excel VBA, Set Horizontal Alignment, (Sample Code)
- Excel VBA, Get Horizontal Alignment (Sample Code)
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
One thought on “Excel VBA, Horizontal Alignment”