VBA & Macro Problems

How to Split a Long Excel VBA Range Statement Across Multiple Lines

Camila Milosovich Sep 27, 2026 869 views

Question details

The user needs to format a long, comma-separated Range address in Excel VBA by splitting it across multiple lines for better code readability.

How to Split a Long Excel VBA Range Statement Across Multiple Lines

Product: Excel VBA
Device & OS: not provided
Scenario: Writing a VBA macro that references a very long range with multiple non-contiguous cells, requiring line breaks in the code editor without causing syntax errors.
Observed behavior: VBA throws a syntax error when attempting to insert a standard line break directly inside a quoted string.

Before you start

Before editing your code, open the VBA editor and ensure that the target cells in your range address correctly match your worksheet layout.

Solution 1Recommended

Split the Quoted String using Ampersand and Underscore

Use the ampersand (&) to concatenate strings and the underscore (_) line-continuation character outside the quotation marks.

VBA does not allow you to press Enter in the middle of a quoted string. To wrap a long string across multiple lines, you must close the quote on the first line, use the ampersand (&) followed by a space and an underscore (_), and then begin a new quoted string on the next line.

Locate the long string

Identify your long Range string in the VBA editor, for example: Range("B4:C4, B7:C7, B8:C8, B9:C9, B12:C12").

Insert quotation marks

Pick a logical break point, usually after a comma. Close the first part with a double quotation mark, and start the second line with a new double quotation mark.

Add the continuation operators

Between the two lines, insert a space, an ampersand (&), another space, and an underscore (_). Hit Enter after the underscore.

Review the final syntax

Ensure your code follows this format: Set Answers = Range("B4:C4, B7:C7, B8:C8, B9:C9, " & _ "B12:C12, B13:C13, B16:C16").

Split the Quoted String using Ampersand and Underscore

Syntax check: Always make sure there is a blank space right before the underscore character; otherwise, VBA will not recognize it as a line continuation and will return a syntax error.

Edit and Run VBA Macros Easily with WPS Spreadsheet

WPS Spreadsheet offers robust support for VBA macros, allowing you to write, edit, and execute your custom scripts smoothly. It is highly compatible with Microsoft Excel's VBA syntax, meaning your split range statements will run perfectly without modification.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled workbook.
2. Access Developer Tools: Go to the 'Developer' tab on the ribbon menu. If it is hidden, you can enable it in the software settings.
3. Launch the VBA Editor: Click on 'Visual Basic' or press Alt + F11 to launch the macro code editor.
4. Edit your code: Locate your macro module and apply the string concatenation and line continuation formatting to your Range statements.

Fully supports VBA macro editing and executionHigh compatibility with Microsoft Excel (.xlsm) macro-enabled formatsLightweight application with a familiar ribbon interfaceBuilt-in advanced developer tools available for custom workflows

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a syntax error when using the underscore in VBA?

A syntax error usually occurs if you forget to put a space before the underscore character, or if you place the underscore inside the quotation marks. The underscore must always reside outside the string quotes and be preceded by a space.

Can I split other long VBA statements using this same method?

Yes, the space-and-underscore ( _) line-continuation method can be used to split almost any long statement in VBA. This includes complex cell formulas, SQL queries, or long conditional IF statements.

Is there a limit to how many line continuations I can use in a single VBA statement?

Yes, VBA allows a maximum of 25 consecutive line-continuation characters ( _) in a single logical statement. If your string is longer than that, you should consider breaking it into multiple string variables and joining them together.

Excel & Data Problems