How to set and use empty range in VBA?

Question

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)

Answer 1

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.)

Answer 2

I used an extra variable to fix this problem. I did not use If EmptyRange Is Nothing but if my counter, j, = 0 then NewRange = SomeRange. Here is my code:

Public Sub copyLineData(line_array As Variant)
' Copys data from the line into the right sheet

Dim i As Integer
Dim j As Integer
Dim line As String
Dim rgn_data As Range
Dim rgn_selected As Range

Dim table_data As ListObject

Set rgn_data = getDynamicRangeFromSheet(Worksheets("Data"), "A1")
Set table_data = Sheets("Data").ListObjects.Add(xlSrcRange, rgn_data, xlListObjectHasHeaders:=xlYes)

' Get the selected rows
For i = 0 To ArrayLen(line_array) - 1
    line = line_array(i)
    ' Make selection
    table_data.Range.AutoFilter Field:=1, Criteria1:=line
    ' Copy data
    j = 0
    For Each Row In table_data.DataBodyRange.Rows
        If Row.EntireRow.Hidden = False Then
            If j = 0 Then
                Set rgn_selected = Row
            Else
                Set rgn_selected = Union(Row, rgn_selected)
            End If
             j = j + 1
        End If
    Next Row
    ' Copy selection
    rgn_selected.Copy Destination:=Sheets(line).Range("A1")
Next i
' Remove selection
table_data.Range.AutoFilter
' Convert back to range
table_data.Unlist
End Sub

Answer 3

You still can use Application.Union using Null as EmptyRange.

Dim ActualRange As Range: Set ActualRange = ThisWorkbook.Sheets(1).Cells(1,1)
EmptyRange = Null
Result = Union(ActualRange, ActualRange, EmptyRange)

The trick is to supply ActualRange twice (as Union wants two mandatory parameters and don't mind if they are the same) and use third optional parameter to do the magic.

Then if you need to start from nothing you can do this:

MyRange = Null
For each Cell In SomeRange.Cells
    If ThisIsMyCell(Cell) Then MyRange = Union(Cell, Cell, MyRange)
Next

Answer 4

The Union() method requires at least 2 named ranges. It combines the two named ranges into one master range. If your true goal is to combine SomeRange with an Empty range, then you should just write:

Set NewRange = SomeRange

Your use of the Union() method is pointless because Union() requires two DEFINED ranges.

http://msdn.microsoft.com/en-us/library/office/aa213609%28v=office.11%29.aspx