VBA & Macro Problems

How to Add Multiple Lines to Excel Headers and Footers Using VBA

Camila Milosovich Oct 20, 2026 869 views

Question details

The user wants to programmatically insert multiline text into Excel headers or footers using VBA, ensuring the text correctly wraps into separate lines rather than collapsing into a single line or rendering improperly.

How to Add Multiple Lines to Excel Headers and Footers Using VBA

Product: Excel
Device & OS: not provided
Scenario: Writing a VBA macro to format page headers and footers for printing or reporting, specifically attempting to display multiple distinct lines of text in a single header section.
Observed behavior: Using standard line breaks (like vbCrLf) can result in double line breaks or text rendering as a single line. Furthermore, scripts may fail if the header or footer exceeds Excel's strict 255-character limit.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) before adding or running VBA scripts. Also, verify that the total text length for each header or footer section does not exceed the 255-character limit to prevent run-time errors.

Solution 1Recommended

Use the vbLf Constant with an Array for Reliable Line Breaks

Using the vbLf character ensures your text splits correctly without adding unwanted double spacing in the header or footer.

When dealing with Excel page setups via VBA, using the standard carriage return and line feed (vbCrLf) often produces an extra blank line. To achieve a clean, single line break, you must use the vbLf constant. Passing an array of strings into the Join function is the cleanest way to construct this.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications window in your active workbook.

Insert a new module

Click "Insert" from the top menu bar and select "Module" to create a blank script window.

Enter the VBA code using the Join function

Type the following code into the module: ActiveSheet.PageSetup.CenterHeader = Join(Array("Line 1", "Line 2", "Line 3", "Line 4"), vbLf)

Run the macro and verify

Press F5 to execute the script. Go back to your worksheet and click "File" > "Print" to check the Print Preview and confirm your multiline header displays perfectly.

Use the vbLf Constant with an Array for Reliable Line Breaks

Character Limit Reminder: Remember that Excel restricts each header and footer section (Left, Center, Right) to a maximum of 255 characters, including the vbLf characters.

Solution 2

Concatenate Strings Using the Ampersand Operator

If you are dynamically generating header text instead of using a static array, basic string concatenation is an effective alternative.

Advanced Macro Support in WPS Office

Run VBA Macros Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for VBA macros, allowing you to easily configure multiline headers and footers using the exact same vbLf scripts. You can fully automate your reporting, page setups, and print formatting at no extra cost.

1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the header data.
2. Access the Developer Tools: Go to the "Developer" tab on the main ribbon and click "VBA Editor" (or press ALT + F11).
3. Input the macro: Paste your ActiveSheet.PageSetup code using the vbLf line break into a standard module.
4. Execute the code: Run the macro and use the "Print Preview" feature to confirm your multiline headers and footers are perfectly formatted.

Fully compatible with Microsoft Excel VBA scripts and macrosNative support for Excel macro-enabled formats (.xlsm, .xltm)Advanced print setup features for professional reportingLightweight, fast, and runs smoothly on all major operating systems

Frequently Asked Questions

Why does my VBA header text display as one long line?

This happens when the wrong line-break character is used or omitted entirely in your VBA code. Standard text requires an explicit line-break command. Using the vbLf constant between text blocks forces Excel to render them on separate lines.

Can I use vbCrLf instead of vbLf for Excel headers?

It is not recommended. While vbCrLf works in many standard VBA message boxes and strings, using it specifically for Excel headers and footers typically results in two line breaks, creating unwanted extra vertical space. Stick to vbLf for a standard single line break.

What happens if my multiline header exceeds 255 characters?

Excel limits each header and footer section (Left, Center, Right) to exactly 255 characters. If your VBA script attempts to assign a string longer than this limit, the text will be abruptly truncated, or it may throw a run-time error.

Excel & Data Problems