Aligning Cells in Excel VBA

Aligning Cells in Excel VBA

The following VBA procedure demonstrates how to align text horizontally and vertically, wrap long text, merge cells, and rotate text within a worksheet.

VBA Example: Aligning and Formatting Cells

Sub FormatCellAlignment()
    ThisWorkbook.Worksheets("Sheet2").Activate
    ' Center text horizontally and align it to the top vertically
    Range("C1").Value = "Hello"
    Range("C1").HorizontalAlignment = xlCenter
    Range("C1").VerticalAlignment = xlTop
    ' Wrap longer text within a single cell
    Range("C2").Value = "This is a longer piece of text"
    Range("C2").WrapText = True
    ' Merge multiple cells and display text vertically
    Range("C3").Value = "Hello"
    Range("C3:C7").MergeCells = True
    Range("C3:C7").Orientation = xlVertical
    ' Rotate text at an angle of 45 degrees
    Range("C8").Value = "Hello"
    Range("C8").Orientation = 45
End Sub

Explanation of the Procedure:

Activating the Worksheet:
The procedure starts by activating the worksheet named « Sheet2 ».

Horizontal and Vertical Alignment:

Range(« C1 »).HorizontalAlignment = xlCenter

Range(« C1 »).VerticalAlignment = xlTop

• • Aligns the text centered horizontally and top-aligned vertically.
  • Common constants for alignment:
    • Horizontal:
      • xlLeft → left-aligned
      • xlRight → right-aligned
      • xlCenter → centered
      • xlJustify → justified (blocks of text)
    • Vertical:
      • xlTop → aligned at the top
      • xlCenter → vertically centered
      • xlBottom → aligned at the bottom

Text Wrapping:

Range(« C2 »).WrapText = True

• • Enables automatic line breaks within the cell when the text is too long to fit.
  • If set to False, the text will remain on a single line, possibly overflowing beyond the cell’s edge.

Merging Cells and Displaying Text Vertically:

Range(« C3:C7 »).MergeCells = True

Range(« C3:C7 »).Orientation = xlVertical

• • Merges the cell range C3:C7 into one large cell.
  • Sets the text orientation to vertical, so the characters appear stacked one below the other.

Rotating Text at a Custom Angle:

Range(« C8 »).Orientation = 45

1. • Rotates the cell content to a 45-degree angle.
   • Orientation accepts:
     • Values from -90 to +90 degrees
     • The default is 0 (horizontal)
     • Special constant: xlVertical for vertical character stacking

Summary of Key Properties:

Result Preview :

• C1: « Hello », centered horizontally and aligned to the top.
• C2: Long text that wraps into multiple lines.
• C3:C7: Merged block showing « Hello » vertically.
• C8: « Hello » displayed at a 45° angle.