32. Format Cells (Alignment)

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
WrapText	Wrap	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
AddIndent	Automatically indent	False	not
IndentLevel	Indent level	0 ～ 15
ShrinkToFit	Automatically shrink	True	fo
ShrinkToFit	Automatically shrink	False	not
ReadingOrder	Reading order	xlRTL	Right to left
		xlLTR	Left to right
		xlContext	Depends on first character
MergeCells	Merge cells	True	do
MergeCells	Merge cells	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

Home >

Introduction to Excel VBA >

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.