VBA Union in Excel — Combine Non-Adjacent Ranges into One Reference (and Why It Doesn't Deduplicate)

TL;DR — Union glues scattered rectangles into one reference so you can act on several non-adjacent blocks at once. It is the code version of Ctrl-clicking multiple areas. The rule that matters most: Union concatenates, it does not deduplicate — overlapping cells are counted twice, so it is a batch list, not a mathematical set union. Its most useful form is the accumulate pattern, which collects cells in a loop and acts on them once:

Dim flagged As Range, c As Range
For Each c In ThisWorkbook.Worksheets("Data").Range("B2:B1000")
    If c.Value < 0 Then
        If flagged Is Nothing Then Set flagged = c Else Set flagged = Union(flagged, c)
    End If
Next c
If Not flagged Is Nothing Then flagged.Interior.Color = vbRed   ' color every match in one call

Every reference so far has held one rectangle. Union breaks that limit: one range variable can now hold B2, D5:D9, and F1 at the same time, and a single line colors, clears, or copies all three. Where SpecialCells lets Excel pick a non-contiguous set for you, Union lets you build one deliberately. This guide is built on one idea — Union is how you assemble a batch to operate on once, not a way to compute a deduplicated set. Get that and the .Count surprise and the Nothing error both make sense.

What you'll learn

• The mental model — Union combines non-adjacent ranges into one reference (Ctrl-click in code)
• The rule that matters most — it concatenates, not deduplicates, so overlaps count twice
• The accumulate pattern — If Is Nothing ... Else Union and why it avoids a runtime error
• Batch operations — color, clear, or copy many blocks in one call instead of a loop
• The limits — at least two arguments, no Nothing, all areas on the same sheet
• How Union (∪, combine) differs from Intersect (∩, overlap)

The mental model: Union combines non-adjacent ranges into one reference

Union takes two or more ranges and returns a single range that covers all of them:

Dim multi As Range
Set multi = Union(Range("A1:A3"), Range("C1:C3"), Range("E1"))
multi.Interior.Color = vbYellow   ' three separate blocks, highlighted together

That reference behaves like any other range for most operations — one .Interior.Color, one .ClearContents, one .Copy touches every block. Think of selecting cells by hand while holding Ctrl: you click A1:A3, then C1:C3, then E1, and now they move as a group. Union is that Ctrl-click expressed in code, and it is the only way to hand a single non-adjacent selection to one operation.

The rule that matters most: Union concatenates, it does not deduplicate

Here is where the name misleads people. In set theory a "union" removes duplicates. VBA's Union does not. It stitches the areas together as-is, so cells that appear in more than one argument are counted twice:

Dim u As Range
Set u = Union(Range("A1:A5"), Range("A3:A8"))
Debug.Print u.Count    ' prints 11  (5 + 6), NOT 8 - A3:A5 counted twice

The overlap A3:A5 is included from both arguments, so .Count reports 11 for what is only 8 distinct cells. This is harmless when you are operating on the reference — setting a color on the same cell twice does nothing bad — but it is a real bug if you .Count the result, sum it, or feed it somewhere that assumes distinct cells. The takeaway: use Union to assemble cells to act on, never to compute how many distinct cells you have. If you genuinely need a deduplicated set, filter the data with an AutoFilter instead.

The accumulate pattern: build a reference in a loop

The single most valuable use of Union is collecting matches during a loop and acting on them once at the end. But there is a catch: Union requires at least two real ranges and rejects Nothing, so Union(rngAll, c) fails on the first iteration when rngAll is still empty. The standard idiom guards the first cell:

Dim rngAll As Range, c As Range
For Each c In Range("A2:A1000")
    If Len(Trim(c.Value)) = 0 Then                 ' whatever your condition is
        If rngAll Is Nothing Then
            Set rngAll = c                          ' first match: seed it directly
        Else
            Set rngAll = Union(rngAll, c)           ' later matches: extend the reference
        End If
    End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete   ' one delete for every match

Read the guard as: seed the reference with the first match, then keep unioning the rest onto it. Skipping the If rngAll Is Nothing branch is the number-one Union error — passing Nothing as an argument raises a runtime error the moment the loop finds its first match.

Batch operations: one call instead of a per-cell loop

Why bother assembling a reference at all, instead of just acting on each cell as you find it? Speed. Touching the worksheet once per cell is the number-one reason macros crawl. Collect the cells with Union, then perform one operation on the whole group — decide in the loop, apply to the range:

' Slow: writes to the sheet on every matching row
For Each c In rng
    If c.Value > 100 Then c.Interior.Color = vbGreen   ' one sheet touch per match
Next c

' Fast: collect first, color once
For Each c In rng
    If c.Value > 100 Then _
        Set hot = IIf(hot Is Nothing, c, Union(hot, c))
Next c
If Not hot Is Nothing Then hot.Interior.Color = vbGreen   ' a single sheet touch

This is the same discipline the cell-color guide recommends for highlighting: gather the targets, apply the format once. On thousands of rows the difference is seconds versus milliseconds.

The limits: two arguments, no Nothing, same sheet

Three constraints catch people, and all three are quick to state:

• At least two ranges. Union needs two or more arguments and none of them may be Nothing — hence the accumulate guard above.
• Same worksheet. Every range you union must live on the same sheet. Combining ranges from two sheets raises error 1004; if you need cross-sheet work, process each sheet's union separately.
• The result is multi-area. Like SpecialCells, a union is a non-contiguous reference. Loop For Each area In u.Areas when you need each contiguous block, and remember .Count totals every cell across all areas (double-counting overlaps).

Union vs Intersect: combine versus overlap

Union and Intersect are the two set operators on ranges, and they are opposites. Union(A, B) returns everything in either A or B — the combined footprint. Intersect(A, B) returns only what is in both — the overlap, or Nothing if they do not touch. Union grows a reference; Intersect shrinks it to the shared part. Reach for Union to assemble scattered cells into one batch, and for Intersect to test or restrict — most famously to ask "did the user edit a cell inside the range I care about?"

How ExcelMaster helps

Union is deceptively simple: the accumulate guard is easy to forget on the first iteration, the .Count double-count silently corrupts any tally, and "same sheet only" surfaces as a 1004 you did not expect. None of these show up until the exact input that triggers them.

ExcelMaster lets you describe the result instead. Say "highlight every row where the balance is negative" or "delete all the blank rows in column A," and it writes the loop, seeds the reference with the first match, extends it with Union, and performs one operation on the whole group — the fast, guarded version, without the Nothing crash or the per-cell slowdown. You keep the workbook and the code; you skip the pass where the loop touched the sheet ten thousand times.

Frequently asked questions

What does the Union method do in Excel VBA?

Union combines two or more ranges into a single reference that covers all of them, even when they are not next to each other. You can then color, clear, copy, or delete every block in one operation. It is the code equivalent of Ctrl-clicking several areas on a sheet so they act as one selection.

Does VBA Union remove duplicate cells?

No. Union concatenates the ranges without deduplicating, so a cell that appears in more than one argument is counted twice. Union(Range("A1:A5"), Range("A3:A8")).Count returns 11, not 8. This is harmless when you operate on the reference but wrong if you count or sum it — use Union to assemble cells to act on, not to compute a distinct set.

Why does Union throw an error in a loop?

Because Union requires at least two real ranges and rejects Nothing. On the first iteration your accumulator variable is still Nothing, so Union(rngAll, c) fails. Guard it: If rngAll Is Nothing Then Set rngAll = c Else Set rngAll = Union(rngAll, c) — seed with the first match, then union the rest.

Can Union combine ranges from different worksheets?

No. All ranges passed to Union must be on the same worksheet; mixing sheets raises error 1004. If you need to work across sheets, build and process a separate union for each sheet.

What is the difference between Union and Intersect in VBA?

They are opposite set operations on ranges. Union returns everything in either range — the combined area. Intersect returns only what is in both — the overlap, or Nothing if they do not touch. Use Union to assemble scattered cells into one batch, and Intersect to test whether one range falls inside another.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-16.

Related guides: VBA Intersect · VBA SpecialCells · VBA Range · VBA Cell Color · VBA For Loop