Excel 2007/2010 – Cell Content Alignment

When reviewing cells, we found out that a cell doesn't have dimensions of its own. Its width is imposed by its parent column and its height is set on its parent row. All of the cells we have used so far were considered individually. Microsoft Excel allows you to combine various cells in a group. This is referred to as merging cells.

To merge cells, select them and:

  • On the Ribbon, click Home. In Alignment section, click the Merge & Center button 
  • On the Ribbon, click Home. In Alignment section, click the button on the right side of Merge & Center and select from the list;
  • Right-click the selected cells and click Format Cells. In the Alignment property page, click the Merge Cells check box and click OK;

To programmatically merge some cells, first select them and access the MergeCells Boolean property. Then assign True or False depending on your intentions.

Practical Learning: Merging Cells

Change the code as follows:

Sub CreateWorkbook()

    . . . No Change

    Rem Merge the cells H15, I15, H16, and I16
    Range("H15:I16").MergeCells = True

    Rem Hide the gridlines
    ActiveWindow.DisplayGridlines = False
End Sub

Cells Content Alignment

Since a cell is really a rectangular box, you can completely control how text is displayed inside of it: left, center, right, top, middle, or bottom.

To visually specify the alignment of text inside of one or more cells, give focus to the cell or select the cells. Then, in the Alignment section of the Home tab of the Ribbon, click the desired alignment button.

To programmatically align the text of a cell or a group of cells, access that cell or the group of cells, access either the HorizontalAlignment or the VerticalAlignment property, and assign the desired value to it.

Practical Learning: Controlling Cells Alignment

Change the code as follows:

Sub CreateWorkbook()
    
    . . . No Change
    
    Rem Merge the cells H15, I15, H16, and I16
    Range("H15:I16").MergeCells = True
    Rem Align the merged text to the left
    Range("H15:H16").VerticalAlignment = xlCenter
    
    Rem Hide the gridlines
    ActiveWindow.DisplayGridlines = False
End Sub

Cells Content Indentation

In the previous section, we used the Center button to center the content of a cell with regards to the width of the cell. In some circumstances, you may not want to center text but you would not like to keep it left or right aligned. Indentation consists of "pushing" text to the left or the right without centering it.

To visually indent the contents of a cell or of a group of cells, after making the selecting, on the Ribbon, click Home. In the Alignment section:

  • Click the Increase Indent button to "push" the contents of a cell or a group of cells to the right
  • Click the Decrease Indent button to "push" the contents of a cell or a group of cells to the left

To programmatically indent the content of a cell or the contents of various cells, refer to that cell or to the group of cells and access its IndentLevel property. Then assign the desired value. Here is an example:

Range("A1").IndentLevel = 5

The Alignment Property Page

Besides using the alignment buttons on the Ribbon, to visually be more precise or to visually perform various actions in one step, you can use the Alignment property page of the Format Cells property sheet.

To provide the same options as the Ribbon, the Alignment property page is equipped with the Horizontal combo box. The Vertical combo box provides options not available on the Ribbon. It allows you to align the contents of a cell towards the top, the middle or the bottom area of a cell.

SOURCE

LINK

LANGUAGE
ENGLISH