Return to VBA Code Examples

VBA Center Text – Cell Alignment (Horizontal & Vertical)

This tutorial will demonstrate how to use VBA to Center Text in Cells both Horizontally and Vertically.

We can use the Alignment group in the Home Ribbon in Excel to center text both horizontally and vertically in a cell. If we are writing a macro to format text, we can re-create this functionality using VBA Code.

Center Text Horizontally

To Center Text horizontally in a single cell, we can use the following code:

Alternatively, to center text horizontally in each cell of a selected range of cells, we can use the Selection object and do the following:

We can also change the alignment to right or left using the xlLeft and xlRight constants.

To right align the text in a cell, we can therefore use the following code:

Center Text Vertically

Centering the text vertically is much the same as horizontally.

As is centering text vertically across a selection:

We can also change the text to the Top or Bottom of a cell or selection using the xlTop or xlBottom constants.

Center Text Horizontally and Vertically at the Same Time

If we want to center the text both Horizontally and Vertically at the same time, there are a couple of ways we can do so.

To cut down on repeating code, we can use a With and End With Statement.

The code above will apply to all the cells in Excel that are selected at the time.

CenterText With

Using With and End With is very effective when we have a lot of formatting to do within the selection, such as merging cells or changing orientation.

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!