[FIXED] How to set and use empty range in VBA?

Issue

I would like to use empty range in following manner :

Set NewRange = Union(EmptyRange, SomeRange)

I've tried to set EmptyRange as empty range using Nothing, Empty and Null but "run-time error '5' Invalid procedure call or argument" occurs, it seems that I have to use If statement or there is other keyword which do the job ?

I can use :

If EmptyRange Is Nothing Then
   Set NewRange = SomeRange
Else
   Set NewRange = Union(EmptyRange, SomeRange)
End If

instead of construction:

Set NewRange = Union(EmptyRange, SomeRange)

Solution

I use this function as a replacement for Application.Union when I need to combine several range objects, where "zero or more" of the ranges might be Nothing:

Function union(ParamArray rgs() As Variant) As Range
  Dim i As Long
  For i = 0 To UBound(rgs())
    If Not rgs(i) Is Nothing Then
      If union Is Nothing Then Set union = rgs(i) Else Set union = Application.union(union, rgs(i))
    End If
  Next i
End Function

Example Usage:

Sub demo_union()
  Dim rg1 As Range, rg2 As Range, rg3 As Range, newRg As Range
  Set rg1 = Range("A1")
  Set rg3 = Range("C3")
  Set newRg = union(rg1, rg2, rg3)
  newRg.Select
End Sub

---

Below is a variation that does not duplicate overlapping cells in the returned range.

Normally when combining overlapping ranges (eg., A1:B2 and B2:C3) with Application.Union (or the function above), the result will have multiple copies of the overlapping cells.

For example using,

Application.Union([A1:B2], [B2:C3]).Cells.Count '8 cells (repeats B2)

↑ ...returns 8 cells: A1 B1 A2 B2 B2 C2 B3 C3
(and a For Each loop will have 8 iterations.)

Function union2 (below) solves this issue by returning only unique cells, and also handles empty ranges (without producing an annoyingly-vague "Invalid Procedure call or argument")

Debug.Print union2([A1:B2], [B2:C3]).Cells.Count '7 cells

↑ ...returns 7 cells: A1 B1 A2 B2 C2 B3 C3
(For Each loop will have 7 iterations.)

Answered By - ashleedawg

Answer Checked By - Robin (PHPFixing Admin)