VBA basics
32. Format Cells (Alignment)

Excel macro basics and applications, introduction to Excel VBA
last updated:09-01-2019

32. Format Cells (Alignment)

Some of this topic was processed by machine translation. Original

Macro VBA can be used to specify the position (vertical position, horizontal position) for displaying the value in the cell.
It is the content specified by "Cell Formatting" → "Placement".

Specifying placement in macro VBA

Range.property = setting value
The property settings that can be specified are as follows.

Property Property description Setting value Setting value description
HorizontalAlignment Horizontal position xlCenter Center
xlDistributed Even allocation
xlJustify Justified
xlLeft left
xlRight right
VerticalAlignment Vertical position xlBottom under
xlCenter center
xlDistributed Even allocation
xlJustify Justify
xlTop top
WrapText Wrap True do
False not
Orientation Character orientation -90 ~ 90 Angle(°)
xlDownward downward
xlHorizontal horizontal
xlUpward upward
xlVertical The string is placed downward and in the center of the cell.
AddIndent Automatically indent True do
False not
IndentLevel Indent level 0 ~ 15
ShrinkToFit Automatically shrink True fo
False not
ReadingOrder Reading order xlRTL Right to left
xlLTR Left to right
xlContext Depends on first character
MergeCells Merge cells True do
False not

To distribute the strings evenly,
Set the VerticalAlignment property to xlVAlignDistributed when the Orientation property is xlVertical,
When the Orientation property is xlHorizontal, set the HorizontalAlignment property to xlHAlignDistributed.

It's difficult to remember all of this and it doesn't make much sense.
There is a macro record just to check these easily.
You can copy and paste the necessary parts from the macro created by recording the macro.
However, for that purpose, understand the meaning of the property (as an English word)
You need to be able to copy and paste only the necessary parts.

Examples of placement in macro VBA

Merge cells to center

Range("Range of cells").HorizontalAlignment = xlCenter
Range("Range of cells").VerticalAlignment = xlCenter
Range("Range of cells").MergeCells = True

MergeCells is a property of Range,
There are also methods for merging cells.
Range("Range of cells").MergeCells = True
Range("Range of cells").Merge
You can still combine them.

As I wrote earlier, there is no need to remember the settings here.
It is enough to be able to check when it is needed.

Articles with the same theme "VBA basics"

29. Delete / Insert cell / row / column (Delete, Insert)
30. General practice question 3
31. Format Cells (display format, NumberFormatLocal)
32. Format Cells (Alignment)
33. Format Cells (Font)
34. Format Cells (fill, interior)
35. Format Cells (ruled line, border)
36. General practice question 4
37. Specifying a book sheet
38. Set formula in cells
39. Clear the cells

  • >
  • >
  • 32. Format Cells (Alignment)

  • If you find this site useful, please share and bookmark.

    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.

    If you find this site useful, please share and bookmark.
    To the bottom of the text