Excel VBA: How to Center a Range Horizontally

VBA & macros · Updated November 21, 2026

To center the contents of every cell in an Excel range horizontally with VBA, set the range’s HorizontalAlignment property to xlCenter. For example, Range("A1:D1").HorizontalAlignment = xlCenter centers each cell’s contents in its own cell; use xlCenterAcrossSelection instead if you want text in the first cell to appear centered across several adjacent cells without merging them.

Center each cell in a range

The simplest approach is to identify the worksheet range and assign its horizontal alignment directly. This example centers every cell from B2 through E10 on a worksheet named Report:

Sub CenterEachCell()
    Dim ws As Worksheet
    Dim target As Range

    ' Change the worksheet name and range to match your workbook.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set target = ws.Range("B2:E10")

    target.HorizontalAlignment = xlCenter
End Sub

In this example, the text or value in B2 is centered within B2, the contents of C2 are centered within C2, and so on for every cell in the range. The same applies to each row down to row 10. It does not combine the cells or center the whole range as one large area.

For a quick one-off change, a single-line instruction is enough: Worksheets("Report").Range("B2:E10").HorizontalAlignment = xlCenter. In a reusable macro, assigning the worksheet and range to variables makes the target clearer and easier to change. Using an explicit worksheet reference also avoids accidentally formatting a different sheet just because it is active.

What horizontal centering means in Excel

Excel stores horizontal alignment as a format property of cells. The VBA property HorizontalAlignment accepts alignment settings such as xlLeft, xlCenter, and xlRight. Setting the property changes how the existing cell content is displayed; it does not change the value, formula, number format, row height, or column width.

A range can contain many cells, but assigning xlCenter applies the setting to each cell separately. This is the same result as selecting the cells and clicking the Center button on the Home tab. If a cell contains a long value and the neighboring cell is occupied, the value may be visually clipped, but centering does not overwrite neighboring content.

Horizontal alignment is different from vertical alignment. If you also need content centered from top to bottom within taller cells, that is a separate property, VerticalAlignment, commonly set to xlVAlignCenter. Keep the two settings distinct: HorizontalAlignment controls left-to-right positioning, while VerticalAlignment controls top-to-bottom positioning.

Center text across columns without merging

A common source of confusion is the difference between centering each cell and centering a heading across several columns. If a title is entered in B2 and you want it to look centered across B2:E2, xlCenter centers the title only inside B2. To center it across the selected cells without merging them, use xlCenterAcrossSelection:

Sub CenterAcrossColumns()
    Dim ws As Worksheet
    Dim target As Range

    ' Put the heading in B2; leave C2:E2 empty.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set target = ws.Range("B2:E2")

    target.HorizontalAlignment = xlCenterAcrossSelection
End Sub

This formatting option makes the content in the leftmost cell appear centered across the selected span. It does not merge the cells, and the heading remains stored in B2. The cells to its right should be empty for the effect to display as intended. If those cells contain values, do not use this layout for them: preserve the data and use ordinary per-cell centering or choose a different heading area.

For a range containing several rows, Excel applies the across-selection alignment row by row. For example, applying it to B2:E4 makes the leftmost cell’s content in each row appear centered across that row’s selected cells. This is not a single title centered over the entire three-row block. For a one-row heading, select only that row’s intended span.

Run the macro in Excel for Microsoft 365

VBA runs in the Excel desktop application, not in Excel for the web. In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. On a Mac, keyboard behavior can depend on the keyboard and function-key settings; use the Developer tab’s Visual Basic command if available, or the Visual Basic Editor command under Excel’s Tools menu.

  1. In the Visual Basic Editor, choose Insert > Module.
  2. Paste the macro into the module and change the worksheet name and range to match your file.
  3. Return to Excel and run the macro from the Macro dialog or from the Visual Basic Editor.
  4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA code in the file.

If the Developer tab is not visible, enable it in Excel’s options: on Windows, use File > Options > Customize Ribbon and select Developer. On Mac, open Excel > Settings or Preferences, choose Ribbon & Toolbar, and enable Developer; labels can vary slightly by release. Excel may also display a security notice when opening a file with macros. Only enable macros in workbooks you trust.

Save a copy of the workbook before running a macro that changes formatting. VBA actions cannot be reversed with Ctrl+Z, so saving a separate copy gives you a straightforward way to recover if the macro targets the wrong cells. Macros can also be run in desktop Excel for Mac, though some VBA features and integrations differ from Windows.

Choose the right alignment method

Use the method that matches what you mean by “center a range.” For a block of ordinary data, the usual choice is to center each cell individually. For a short heading spanning empty columns, Center Across Selection can create a centered appearance without merging cells. Merging is another option, but it changes the worksheet structure and can make selecting, sorting, and editing data less convenient.

  • Center every cell: Set HorizontalAlignment to xlCenter. Each cell keeps its own value and centers it within that cell.
  • Center a heading across a span: Set the range’s alignment to xlCenterAcrossSelection, with the heading in the leftmost cell and the rest of the selected cells empty.
  • Center a merged heading: Merge the cells and center the result only when a merged layout is appropriate for the sheet. Merging is not required for horizontal centering.
  • Center the selection manually: Select the cells and click Home > Alignment > Center. This is often faster when the task does not need to be repeated.

For reports generated repeatedly, VBA helps keep formatting consistent and can be run after the data is refreshed. For a one-time adjustment, using the ribbon avoids adding or maintaining a macro. In either case, be precise about whether the goal is to center each value in its own cell or to position one heading across multiple columns.

Common problems and how to fix them

The macro changes the wrong sheet. A reference such as Range("B2:E10") without a worksheet qualifier refers to the active sheet in many common VBA contexts. If another sheet is active when the code runs, the result may surprise you. Qualify the range with a worksheet variable, as in the examples, and confirm the sheet name is spelled exactly as it appears on the tab.

Excel reports that the subscript is out of range. This often means the worksheet name in Worksheets("Report") does not match a sheet in the workbook identified by ThisWorkbook. Update the name, or use a worksheet’s VBA code name if the macro is designed for a specific workbook. Remember that ThisWorkbook means the workbook containing the macro, which may differ from the workbook currently active.

A heading still appears left-aligned within its original cell. That is expected if you used xlCenter on a multi-cell range: it centers the heading only within the cell where it is stored. Use xlCenterAcrossSelection for a non-merged heading across columns, and check that the cells to the right are blank.

Formatting looks inconsistent in a table or merged area. A worksheet can contain merged cells, table styles, and other formatting that make a broad range harder to predict. Apply the setting to a clearly defined range, avoid spanning unrelated merged and unmerged cells, and test on a copy first. For structured data, avoid merging cells within the data itself; merged layouts can interfere with sorting, filtering, and selecting records.

The macro runs but does not appear to do anything. Check that the range is visible on the intended worksheet, that the macro actually ran, and that the range reference is not empty or misspelled. If the file is open in a browser, switch to desktop Excel because Excel for the web cannot run VBA macros. Also check whether the relevant cells already have centered alignment.

Applying the format safely in larger workbooks

When automating a report, format only the cells that need the change rather than selecting an entire worksheet. A broad assignment can overwrite intentional left or right alignment in labels, notes, and totals. If new rows are added regularly, define the target range from the data or use a named range, but make sure the macro does not include headings or neighboring sections that should retain their own formatting.

It is usually unnecessary to use Select or Activate just to set alignment. Directly assigning the property to a range is shorter and less dependent on which cell happens to be selected. For example, ws.Range("B2:E10").HorizontalAlignment = xlCenter works on the referenced worksheet without changing the user’s active selection.

Alignment is formatting, so this operation does not alter formulas or values. Still, a macro may do more than its visible alignment line if the surrounding procedure contains additional instructions. Review the complete macro before running it, especially if it was copied from another workbook or downloaded. Save a backup first because the normal undo history cannot restore changes made by VBA.

Frequently Asked Questions

What is the VBA code to center a range in Excel?
Use Range("A1:D1").HorizontalAlignment = xlCenter, changing the address to your range. For clearer, more reliable code in a macro, qualify the range with the worksheet that contains it.

Does xlCenter center the whole selected range as one object?
No. It centers the contents of each cell within that individual cell. To center one heading across several adjacent empty cells without merging, use xlCenterAcrossSelection.

How do I center a range vertically as well?
Set the separate VerticalAlignment property to xlVAlignCenter. You can assign both properties to the same range if you want content centered left-to-right and top-to-bottom.

Can I center cells without using VBA?
Yes. Select the cells and choose Home > Alignment > Center. For a heading across cells, open the Format Cells dialog and choose Center Across Selection from the Horizontal alignment options.

Can I run an Excel VBA macro in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.