Formatting Issues

How Excel Column Widths Affect Center Across Selection in VBA

Algirdas Jasaitis Oct 12, 2026 868 views

Question details

The user needs to correct a VBA macro offset issue where Center Across Selection targets the wrong cells due to text spilling into adjacent empty cells.

How Excel Column Widths and Text Spill Affect VBA Center Across Selection

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using an Excel VBA macro to sum column widths and automatically select the remaining center cells to apply the Center Across Selection formatting.
Observed behavior: Because the text width (AutoFit value) is greater than the visual sum of the column widths due to text spilling into empty cells, the macro selects one column too far to the right, misaligning the centered text.

Before you start

Before running or adjusting your VBA macro, ensure you have temporarily turned on gridlines or borders to clearly visualize where your text spill ends, and save a backup copy of your workbook.

Solution 1Recommended

Adjust Column Widths Before Calculating VBA Ranges

Prevent the visual text spill from confusing the macro's column width calculations by ensuring the text fits or adjusting the boundary logic.

Empty cells allow text to overflow natively. This explains why a string may appear to fit perfectly even when the AutoFit width is technically larger than the combined column widths. To use Center Across Selection accurately via VBA, the selected cells must represent the true available center area.

Pre-format the column widths

Before letting the macro calculate the bounds, manually expand the column width of the first cell so the text does not spill, or use the AutoFit feature on the specific column.

Adjust VBA boundary logic

Modify your macro so it accounts for the overflow by checking if the adjacent cell is empty (using `IsEmpty(ActiveCell.Offset(0, 1))`) rather than strictly summing column widths against text width.

Execute Center Across Selection

Once the precise range is selected, apply the formatting by setting `.HorizontalAlignment = xlCenterAcrossSelection` in your VBA script.

Adjust Column Widths Before Calculating VBA Ranges

Text Spill vs AutoFit: AutoFit measures the absolute width of the text string. If text spills, the total visual width spans multiple columns, which makes mathematical width calculations in VBA unreliable unless adjacent empty cells are factored in.

Solution 2

Apply Standard Center Across Selection VBA Formatting

Use this standard VBA snippet to apply Center Across Selection to a pre-defined static range, avoiding dynamic calculation errors.

WPS Spreadsheets

Easily Format and Center Text in WPS Office

WPS Spreadsheets offers full support for 'Center Across Selection' and is highly compatible with Microsoft Excel VBA macros. You can effortlessly format text across multiple columns without the risk of breaking data sorting caused by merged cells.

1. Open your spreadsheet: Launch WPS Spreadsheets and open your workbook.
2. Select the target range: Highlight the row of cells where you want the text to be centered (e.g., A1 through E1).
3. Access Format Cells: Right-click the highlighted area and select 'Format Cells' from the context menu, or press Ctrl+1.
4. Apply the alignment: Navigate to the 'Alignment' tab. Under the 'Horizontal' drop-down menu, choose 'Center Across Selection' and click OK.

Full support for Microsoft Excel VBA macros and scriptsNative 'Center Across Selection' alignment featureSeamless compatibility with .xlsx and .xlsm formatsFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel text spill into adjacent cells?

When a cell contains text that is wider than its column width, the spreadsheet program automatically allows the text to visually spill into the adjacent cells to the right, provided those adjacent cells are completely empty.

What is the difference between Merge Cells and Center Across Selection?

'Merge Cells' physically combines multiple cells into one large cell, which can disrupt sorting, filtering, and VBA macros. 'Center Across Selection' visually centers the text across the highlighted columns while keeping the individual underlying cells intact and functional.

How do I stop text from spilling into the next cell?

You can prevent text from spilling by doing one of three things: enabling 'Wrap Text' within the cell, expanding the column width to accommodate the entire string, or typing a space (or any data) into the adjacent empty cell.

Document & File Issues