CODEWITHSUNDEEP
excelvba
Kevin López
Kevin López

Reputation: 117

Align text to left and right in Excel?

I have a workbook in which I need to align text (from cells) to the left and the to the right. I have this so far but I don't know how to proceed.

Sub M()

ActiveSheet.PageSetup.CenterHeader = ActiveSheet.Range("A1") & " " & 
ActiveSheet.Range("B1")

End Sub

I want to know in general how to override the alignment for text in each part of the header but in this instance, I need to have text aligned to the left in both the right and left headers.

Upvotes: 4

Views: 85160

Answers (5)

Sandy
Sandy

Reputation: 71

I found you can use .api to center / left / right align.

  • Set the value of the HorizontalAlignment property of the cell to:
    • Center-aligned, we’d set HorizontalValue to -4108;
    • Right-aligned, we’d set HorizontalValue to -4152;
    • Left-aligned, we’d set HorizontalValue to -4131;

What it would look like:

import xlwings as xw
sht = xw.sheets.active
sht.range(f'$A1:$C5').api.HorizontalAlignment = -4131

Upvotes: 1

Jsleshem
Jsleshem

Reputation: 735

To do this in Excel, select the section of cells needed to align. Then, type alt+H+A+L for left, alt+H+A+C for center, and alt+H+A+R for right.

However, based on your question it seems like you want to do this in VBA instead of Excel. If that is the case, do this instead:

Range(myRange).HorizontalAlignment = xlRight for right, and Range(myRange).HorizontalAlignment = xlLeft for left, where myRange is the range of cells.

Upvotes: 4

YowE3K
YowE3K

Reputation: 23974

I am interpreting your question as "I want to have one cell's value as the left part of my header, and another cell's value as the right part of my header".

If so, you probably want:

Sub M()
    With ActiveSheet.PageSetup
        .LeftHeader = ActiveSheet.Range("A1").Value
        .CenterHeader = ""
        .RightHeader = ActiveSheet.Range("B1").Value
    End With
End Sub

Upvotes: 2

Zachary Summers
Zachary Summers

Reputation: 31

The HorizontalAlignment property of the Range should be what you're looking for. xlLeft or xlRight are values to align left or right.

Range.HorizontalAlignment = xlLeft

or

Range.HorizontalAlignment = xlRight

E.G.

Sub M()

ActiveSheet.PageSetup.CenterHeader = ActiveSheet.Range("A1") & " " & 
ActiveSheet.Range("B1").HorizontalAlignment = xlRight

End Sub

would align the B1 cell to the right.

Upvotes: 3

BruceWayne
BruceWayne

Reputation: 23283

You can use Range("A1").HorizontalAlignment = xlLeft or ... = xlRight

Of course, adjust the range(s) as necessary.

Upvotes: 0

Related Questions