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:

Jump To:


Left (Indent), xlLeft:

The following code will left align the text in cell “A1”:

Range("A1").HorizontalAlignment = xlLeft

Result:

Left (Indent) XlLeft


Center, xlCenter:

The following code will apply the horizontal center alignment to the text in cell “A1”:

Range("A1").HorizontalAlignment = xlCenter

Result:

Center, XLCenter


Right (Indent), xlRight:

The following code will right align the text in cell “A1”:

Range("A1").HorizontalAlignment = xlRight

Result:

Right (Indent), xlRight, Excel VBA


Fill, xlFill:

The following code will fill the cell in “A1” with the text in it:

Range("A1").HorizontalAlignment = xlFill

Result:

Fill, xlFill, Excel VBA


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:

Justify, xlJustify, Excel VBA


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:

Center Across Selection,xlCenterAcrossSelection , Before Excel VBA

After:

Center Across Selection,xlCenterAcrossSelection , After Excel VBA


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:

Distributed, xLDistributed, Excel VBA

After:

Distributed, xLDistributed, After Excel VBA


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:

General, xLGeneral, Excel VBA


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:

Indent, IndentLevel, Before, Excel VBA

After:
Indent, IndentLevel, After, Excel VBA
See also:

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

Leave a Reply

Your email address will not be published. Required fields are marked *