VBA Excel VerticalAlignment = xlCenter not working

Question

The below code selects the sheet but fails to align the cells to center.

                wb.Sheets(1).Columns("A:L").Select
                With Selection
                    .VerticalAlignment = xlCenter
                End With

Thanks!

wb.Sheets(1).Activate
wb.Sheets(1).Columns("A:L").Select
With Selection
  .VerticalAlignment = xlCenter
End With

Selects the entire sheet but it's not changing the vertical alignment to center.

wb.Sheets(1).Columns("A:L").VerticalAlignment = xlCenter

Does nothing.

I don't want HorizontalAlignment :)

I found out the column has VerticalAlignment set to xlCenter but the Cells underneath the column do not have VerticalAlignment set to xlCenter.

Are you sure you want VerticalAlignment & not HorizontalAlignment? just checking — PaulStock
We'll need more information; this works for me. Are you perhaps intending to use HorizontalAlignment instead of VerticalAlignment? — Jay
This code will work whenever wb.Sheets(1) is the active worksheet otherwise it will fail. I am upvoting this question because it is a common trap for people learning to program VBA through recording macros. — adamleerich
@Bruno: are you still looking for a better answer? If not you should accept one. — adamleerich

Patrick Honorez Patrick Honorez · Accepted Answer · 2011-09-15T15:40:54

Don't Select and don't work with Selection without a reason. That's Recorder's stuff. It is longer to read, slower to execute, and prone to error.

wb.Sheets(1).Columns("A:L").VerticalAlignment = xlCenter is much better.

If you need to do several things with the same range, then use

with wb.Sheets(1).Columns("A:L")
      .VerticalAlignment = xlCenter
      .somethingElse
End with

VBA Excel VerticalAlignment = xlCenter not working

3 Answers