Skip to main content

Formatting Cells Alignment with VBA

Text Alignment


The value of HorizontalAlignment property can be set to one of the XlHAlign constants:

Name Value Description
xlHAlignCenter -4108 Center.
xlHAlignCenterAcrossSelection 7 Center across selection.
xlHAlignDistributed -4117 Distribute.
xlHAlignFill 5 Fill.
xlHAlignGeneral 1 Align according to data type.
xlHAlignJustify -4130 Justify.
xlHAlignLeft -4131 Left.
xlHAlignRight -4152 Right.

The following code sets the horizontal alignment of cell A1 to center.

Range("A1").HorizontalAlignment = xlHAlignCenter

Justify Distributed

The following code sets the horizontal alignment for text in cell A1 on Sheet1 to equal distribution and then indents the text.

With Worksheets("Sheet1").Range("A1")
    .HorizontalAlignment = xlHAlignDistributed
    .AddIndent = True
End With


The following code sets the indent level to 4 in cell A1. Can be an integer from 0 to 15.

With Range("A1")
    .IndentLevel = 4
End With


The value of VerticalAlignment property can be set to one of the XlVAlign constants.

Name Value Description
xlVAlignBottom -4107 Bottom
xlVAlignCenter -4108 Center
xlVAlignDistributed -4117 Distributed
xlVAlignJustify -4130 Justify
xlVAlignTop -4160 Top

The following code sets the vertical alignment of cell A1 to bottom.

Range("A1").VerticalAlignment = xlVAlignBottom

Text Control

Wrap Text

This example use WrapText property formats cell A1 so that the text wraps within the cell.

Range("A1").WrapText = True

Shrink To Fit

This example causes text in row one to automatically shrink to fit in the available column width use ShrinkToFit property.

Rows(1).ShrinkToFit = True

Merge Cells

This example merge range A1:A4 to a large one.

Range("A1:A4").MergeCells = True


Text direction

The value of ReadingOrder property can be set to one of the XlReadingOrder constants:

Name Value Description
xlContext -5002 According to context.
xlLTR -5003 Left-to-right.
xlRTL -5004 Right-to-left.

The following code example sets the reading order of cell A1 to xlRTL (right-to-left).

Range("A1").ReadingOrder = xlRTL


The value of Orientation property can be set to an integer value from –90 to 90 degrees or to one of the XlOrientation constants:

Name Value Description
xlDownward -4170 Text runs downward.
xlHorizontal -4128 Text runs horizontally.
xlUpward -4171 Text runs upward.
xlVertical -4166 Text runs downward and is centered in the cell.

The following code example sets the orientation of cell A1 to xlHorizontal.

Range("A1").Orientation = xlHorizontal

Leave a comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>