VBA Range Union: Combine Separate Ranges in Excel

In VBA, use Application.Union to combine two or more ranges on the same worksheet into one Range object, even when the cells are not next to each other. For example, assign the result with Set combined = Application.Union(ws.Range(“B2:B5”), ws.Range(“D2:D5”)); you can then format, inspect, or otherwise work with both areas through that object.

What VBA Range Union means

A union is the set of cells covered by multiple ranges. In Excel VBA, the Union method returns a Range object representing those cells, including gaps between them only if a range explicitly covers those gaps. If you combine B2:B5 with D2:D5, the result contains the eight cells in those two blocks; it does not include column C.

This is useful when your task applies to scattered cells or blocks as though they were one target. You might bold several report headings, clear selected input cells, inspect values in separate columns, or apply one format to a few nonadjacent sections. Union does not move cells or change the worksheet merely by creating the reference. Any change happens when your code uses the returned Range object to perform an action.

Basic syntax and worksheet requirements

The method is written as Application.Union(range1, range2, …). Each argument must resolve to a Range object. The method accepts up to 30 range arguments in a single call, and the ranges must belong to the same worksheet. In practice, explicitly qualifying every range with a worksheet makes the code easier to understand and reduces accidental dependence on whichever sheet happens to be active.

Assign the result to an object variable with Set, because the result is an object rather than a value. For example, a variable declared As Range can hold the combined range. If the ranges are on different worksheets, Union cannot create one ordinary Range object spanning both sheets; VBA raises an error. Build separate unions for each sheet instead.

Use a worksheet variable such as ws and refer to its ranges through ws.Range. This avoids a common bug: Range(“B2”) without a worksheet qualifier refers to the active sheet, which may not be the sheet your procedure intended to use. Likewise, ThisWorkbook refers to the workbook containing the VBA code, while ActiveWorkbook refers to the workbook currently active in Excel. Choose deliberately when identifying the workbook.

A working example: combine and format ranges

This macro combines two blocks on a worksheet named Data, makes the selected cells bold, and prints the combined address in the Immediate window. Change the sheet name and range addresses to suit your workbook. The macro changes formatting, so save a copy of the workbook before running it; macros cannot be undone with Ctrl+Z.

Sub FormatSeparateBlocks()
    Dim ws As Worksheet
    Dim combined As Range

    ' Use the worksheet in the workbook that contains this code.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Combine two nonadjacent blocks on the same worksheet.
    Set combined = Application.Union(ws.Range("B2:B5"), ws.Range("D2:D5"))

    ' Apply one formatting change to every cell in the union.
    combined.Font.Bold = True

    ' Show the combined reference in the Immediate window.
    Debug.Print combined.Address
End Sub

To add the code in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Return to Excel, press Alt+F8, select FormatSeparateBlocks, and choose Run. On a Mac, Option+F11 opens the Visual Basic Editor on many keyboards; if function keys control system features, try Fn+Option+F11. Macro availability and prompts depend on workbook format and security settings, so save macro code in a macro-enabled workbook such as an .xlsm file.

Combining more than two ranges

For a small number of areas, pass them directly to Application.Union. The method has a 30-argument limit for one call. If you need to combine more ranges, build the result in stages: first union a pair, then union that result with another range, and continue. Each additional range still needs to be on the same worksheet.

Another approach is to loop through a list of range addresses and update the accumulated Range object. This is handy when the number of areas is determined by data or user choices rather than fixed in the code. Start with the first valid range, then use Application.Union to add each later range. Do not attempt to union an uninitialized Range variable with another range; check that the accumulated object has been set first.

For a handful of fixed ranges, a direct call is usually clearer than a loop. For a long, changing list, a loop can avoid an unwieldy line and make the input easier to maintain. Either way, validate the addresses and ensure they all refer to the same worksheet before building the union.

Working with the result and its areas

A union can have multiple distinct areas. The Areas collection provides access to each contiguous block in the combined Range object. Its Count property tells you how many such areas the object contains. A practical pattern is to loop over Areas when you want to handle each block separately, for example, to report each block’s address or apply an operation that expects one contiguous area.

Many normal Range operations work on a multi-area result, including setting font formatting and examining cells. However, not every Excel operation treats a multi-area range exactly like a single rectangle. Some methods or properties that expect one contiguous block may fail or behave differently when given multiple areas. If an operation reports an error, test it on each area individually rather than assuming every Range method accepts a disjoint selection.

A union combines cell references, not their values. It does not concatenate text, add numbers, or calculate a total. If the aim is to calculate over the selected cells, you must use an appropriate worksheet function or VBA logic after creating the reference. Also remember that a union is a reference to cells, not a stored copy of their contents; changes to those worksheet cells are reflected when the reference is used.

Union versus selecting cells

Application.Union creates a Range object for code to use. It does not need to select or activate anything on the screen. That is generally preferable to selecting cells: code that directly formats or reads a qualified range is less dependent on the user’s current selection and active worksheet.

You can call Select on a Range object if showing a selection is genuinely part of the task, but many procedures do not need to. For example, using combined.Font.Bold = True acts on the target directly. Selecting a range first adds a screen-state change and can cause errors if the intended sheet is not active. Keep selection and activation for interactive tasks, not as a required step in ordinary range processing.

Union is also different from intersection. Union includes cells from either input range. Intersection returns only cells shared by both inputs, if any. For example, two overlapping blocks can have a nonempty intersection, while two separated blocks have no common cells. Use Application.Intersect when the question is “which cells overlap?” and Application.Union when the question is “which cells are in either target?”

Common errors and how to avoid them

Ranges on different sheets: Union requires its range arguments to be on the same worksheet. Check each range’s parent worksheet, or create one union per sheet.
Unqualified references: Range(“A1”) can refer to the active sheet. Qualify references with a worksheet variable so the macro targets the intended sheet.
Missing Set: Assigning an object result without Set causes an object-assignment error. Declare the variable As Range and use Set when assigning the union.
Invalid worksheet or address: A misspelled sheet name or invalid address can stop the procedure. Confirm the worksheet exists and that the addresses match the workbook layout.
Assuming there is one area: A multi-area range may not work with an operation that expects one contiguous block. Inspect Areas.Count or process each area separately.
Confusing selection with the union: Creating a union does not make it the user’s visible selection. Call Select only when that visible behavior is wanted and the worksheet is active.

When diagnosing a problem, step through the procedure in the Visual Basic Editor and inspect the worksheet variable and each range argument before the Union call. A small test with two known ranges can help distinguish an invalid reference from a later operation that does not support multiple areas.

When Union is the right tool

Use Union when the target is a known set of separate cells or blocks on one worksheet and you want to apply the same range-level action to them. It is particularly readable for a few fixed areas, such as applying consistent formatting to report sections or collecting several input ranges for validation.

It may not be the best choice when the target is described by a data rule rather than fixed addresses. For example, if you need every row where a status column says “Overdue,” a loop, filter, or other data-driven method may be more maintainable than constructing a long union of individual cells. Likewise, if you need to work across multiple worksheets, handle each sheet’s range separately rather than trying to make one cross-sheet union.

Before choosing a method, consider whether the action accepts multiple areas, whether the ranges might overlap, and whether the set of targets changes over time. Overlapping inputs do not make duplicate copies of cells in the resulting range; the result represents the combined cell set. If the target list changes frequently, generate it from the worksheet’s current contents rather than hard-coding addresses.

Frequently Asked Questions

Can VBA Union combine ranges from different worksheets?
No. The ranges supplied to one Application.Union call must be on the same worksheet. Create separate Range objects or unions for each worksheet, and process them independently.

Does Union include the cells between two ranges?
No. If you combine B2:B5 and D2:D5, the cells in column C are not included. Union combines the specified cells; it does not automatically fill the gap between them.

How many ranges can Application.Union accept?
A single call accepts up to 30 range arguments. To combine more, build the result through multiple calls, adding further ranges in stages.

Does Union change or select cells?
No. It returns a Range reference and does not itself change worksheet content or selection. Changes occur only when your code uses the returned object, such as when it changes formatting or values.

What is the difference between Union and Intersect in VBA?
Union combines the cells from either range, while Intersect returns only cells shared by both. Use Union to build a broader target and Intersect to test for or work with overlap.

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.