Excel VBA Union: Combining Multiple Ranges in a Single Macro

In Excel VBA, Union combines two or more ranges on the same worksheet into one Range object, even when the cells are not adjacent. For example, assign Application.Union(ws.Range("A1:A3"), ws.Range("C1:C3")) to a Range variable, then format or process both blocks through that variable.

What Union means in Excel VBA

A worksheet Range can describe one continuous block of cells or several separate blocks, called areas. Application.Union creates a single Range object that represents the combined cells. It does not move cells, insert rows, copy data, or change the selection by itself; it gives your code a convenient reference to multiple locations.

This is useful when a macro needs to apply the same operation to scattered cells. Instead of repeating a formatting instruction for each block, you can build a union and apply the operation once. A union can also be passed to other procedures that accept a Range.

The method belongs to Excel’s Application object, so the usual form is Application.Union(range1, range2). The result is still a Range object, not an array of values. Its Areas collection exposes the separate rectangular regions that make up the result.

Basic syntax and requirements

The first argument is required; additional range arguments are optional. Excel’s method accepts up to 30 range arguments in one call. Each argument must refer to a Range on the same worksheet. Passing ranges from different worksheets causes a run-time error rather than creating a union across sheets.

Qualify each range with a worksheet variable or worksheet reference. Unqualified expressions such as Range("A1") refer to the active sheet, which can change while a macro runs. Explicit references make the code easier to understand and reduce the chance that a macro edits the wrong tab.

Here is a complete example that combines two nonadjacent blocks on a sheet named Data and formats them. The workbook must contain that worksheet. Save a copy before running a macro: VBA changes such as formatting cannot be undone with Ctrl+Z.

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

    ' Use the Data worksheet in this workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set combined = Application.Union(ws.Range("A1:A3"), ws.Range("C1:C3"))

    ' Apply one format to both areas.
    combined.Font.Bold = True
End Sub

In this example, cells A1:A3 and C1:C3 become bold; the cells between them are not included. The code does not need to activate the worksheet or select the cells. Using object references directly is generally clearer and less fragile than relying on the current selection.

How a multi-area range behaves

The combined object can contain multiple areas, and those areas remain distinct when they are separated by unselected cells. For the example above, the result has two areas. You can inspect the count with the Range object’s Areas.Count property or loop through the areas when each block needs separate treatment.

When a cell is included in more than one input range, it is represented once in the resulting union; overlap does not create duplicate cells. For instance, combining A1:B3 with B2:C4 gives a single combined range covering the rectangular regions, with the shared B2:B3 cells included once. The result is a range reference, not a list where repeated addresses are preserved.

There is an important distinction between iterating areas and iterating cells. A loop over combined.Areas runs once per contiguous block. A loop over combined.Cells can visit individual cells across those blocks. Choose the level that matches the task: area-level code is convenient for operations on whole blocks, while cell-level code is appropriate when each value must be checked or changed individually.

Some Excel operations behave differently with noncontiguous selections than with one solid rectangle. For example, assigning a two-dimensional array to a multi-area range is not a reliable way to distribute values across all its separate blocks. If your goal is to write different values into scattered cells, address each destination area or cell explicitly and verify the intended mapping.

When to use Union

Union is particularly useful when the ranges are determined by logic or gathered in separate parts of a macro. Common cases include applying a consistent number format to several report sections, clearing input cells that are separated by labels, highlighting several exception columns, or passing a set of relevant cells to a helper procedure.

Shared formatting: combine several report blocks, then apply a font, fill, border, or number format to the result.
Targeted clearing: combine designated input cells before clearing their contents, taking care not to include formulas or headings.
Conditional processing: gather cells found by separate checks and handle the resulting set together.
Reusable procedures: pass a multi-area Range to another Sub or Function that accepts a Range parameter.

Be deliberate about what operation follows the union. A call such as combined.ClearContents removes values and formulas from every included cell, but leaves formatting in place. combined.Clear removes more than contents, including formatting. Those operations can make destructive changes, so confirm the ranges and save a backup before running the macro.

Building a union with more than two ranges

For a small, fixed set of blocks, list them as arguments. The ranges still need to be on the same worksheet, and the Range variable should be declared and assigned before it is used. You can also build a union gradually when the ranges are discovered inside a loop.

In a loop-based pattern, initialize the variable with the first qualifying range; on later matches, replace it with a union of the existing range and the new one. Do not try to union an uninitialized Range variable with the first match. Also consider whether the search might find no matches, and handle that case before using the result. This prevents a later operation from failing because there is no range to process.

For fixed arguments, the method call is easy to read, but a long list can become hard to maintain. If the target addresses are naturally stored in a configuration table, a loop may be more suitable. In either pattern, keeping a worksheet variable in scope and qualifying every address prevents accidental dependence on the active sheet.

Common errors and troubleshooting

Ranges are on different sheets: Union works within one worksheet. If the source cells are on separate sheets, process each sheet’s range separately rather than trying to form one cross-sheet Range.
Object variable not set: a Range variable must be assigned with Set before you use it. Check that the assignment ran and that a search or filter actually found a range.
Worksheet name is incorrect: a misspelled tab name or a sheet that does not exist in the referenced workbook causes an error. Check the workbook and worksheet references.
Wrong sheet is affected: unqualified Range references use the active sheet. Qualify them with the intended worksheet, and use ThisWorkbook when the macro should operate on the workbook containing the VBA code.
Unexpected cells are included: inspect every input address and remember that overlapping ranges are merged. A union includes exactly the cells covered by the supplied ranges, not the empty cells between separate areas.
Nothing seems to happen: creating a union alone does not select, format, or edit cells. Add the intended operation after assigning the combined range.

When debugging, step through the macro in the Visual Basic Editor and examine the worksheet references and range addresses before the operation that changes cells. On Windows, Alt+F11 opens the Visual Basic Editor in desktop Excel. Excel for Mac also supports VBA in the desktop app, though editor access and some keyboard shortcuts can differ by keyboard and settings. Excel for the web cannot run VBA macros.

Union compared with related approaches

Union and Intersect solve different problems. Union returns cells that belong to either supplied range; Intersect returns cells shared by both. If you need the common cells between a selected block and a target block, Intersect is the relevant method. If you want to handle both blocks together, use Union.

A range address can also describe multiple areas directly, such as Range("A1:A3,C1:C3"). This is concise when the addresses are fixed and known in advance. Union is often clearer when ranges are held in variables, assembled conditionally, or need to be combined as the macro runs. Both approaches produce a multi-area Range, and both still require care with worksheet qualification.

For a truly continuous rectangle, use one range reference instead of Union. For example, a single rectangular block is simpler to express as one address than as several adjacent ranges. Reducing needless fragmentation makes code easier to maintain and can avoid surprises in methods that handle multi-area ranges differently.

Safety and workbook compatibility

VBA is available in desktop Excel for Microsoft 365 on Windows and in desktop Excel for Mac, but macros do not run in Excel for the web. To keep VBA code in a workbook, save it in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file does not preserve VBA macros when saved in that format.

Only enable macros from sources you trust. Before a macro that clears, overwrites, or reformats data, save a separate copy and confirm the worksheet name, input ranges, and resulting target cells. Ctrl+Z cannot undo a macro’s changes, so a backup is the practical way to recover if the code affects more than intended.

Frequently Asked Questions

Does VBA Union select the ranges?
No. It returns a Range object that represents the combined cells. To select that range would require a separate selection action, but most macros can work directly with the returned object without selecting anything.

Can Union combine ranges from different worksheets?
No. The ranges passed to one Union call must be on the same worksheet. Run separate operations for each worksheet, or redesign the procedure to handle each sheet independently.

Does Union include the cells between two ranges?
No. If you combine A1:A3 and C1:C3, column B is excluded. Only cells within the specified ranges belong to the resulting range.

Can I use Union with more than two ranges?
Yes. Supply additional range arguments, up to the method’s 30-argument limit in one call, or build a union incrementally when ranges are found dynamically. All ranges must still be on the same sheet.

What is the difference between Union and Intersect?
Union combines the cells from its input ranges. Intersect returns only the cells those ranges have in common. Use the method that matches whether you need the combined set or the overlap.

Can I run an Excel VBA Union macro in a browser?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to use its VBA code.

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.