Skip to main content
Code Review

Return to Answer

Commonmark migration
Source Link
Community Bot
Community Bot
  • 1

[![enter image description here][1]][1]enter image description here

Immediate Window ScreenShot [1]: https://i.sstatic.net/K6U9E.png

[![enter image description here][1]][1]

Immediate Window ScreenShot [1]: https://i.sstatic.net/K6U9E.png

enter image description here

Immediate Window ScreenShot

added 1757 characters in body
Source Link
TinMan
TinMan
  • 4.3k
  • 1
  • 13
  • 25

Addendum

Here was my first attempt at cracking 75 K areas. It performed very well with smaller number of unions but started to slow down exponentially after 20 K unions. Although, it probably isn't practical, there may be some merit to combining it with the FastUnion. If nothing else it was interesting to write.

StingUnion:Class

Option Explicit
Private Const MaxAddressSize As Long = 255
Private CurrentLength As Long
Private Result As Range
Private Parent As Worksheet
Private AddressHolder As String

Public Sub Add(Source As Range)
    If Parent Is Nothing Then
        Set Parent = Source.Parent
        AddressHolder = Space(MaxAddressSize)
    End If
    
    Dim length As Long
    Dim Address As String
    Address = Source.Address(0, 0)
    length = Len(Address)
    
    If (length + CurrentLength) > MaxAddressSize Then Compact
    
    If CurrentLength = 0 Then
        Mid(AddressHolder, CurrentLength + 1, length + 1) = Address
    Else
        Mid(AddressHolder, CurrentLength + 1, length + 1) = "," & Address
    End If
    CurrentLength = CurrentLength + length + 1
    
End Sub

Public Sub Compact()
    If CurrentLength = 0 Then Exit Sub
    
    If Result Is Nothing Then
        Set Result = Parent.Range(AddressHolder)
    Else
        Set Result = Union(Result, Parent.Range(AddressHolder))
    End If
    
    CurrentLength = 0
    AddressHolder = Space(MaxAddressSize)
End Sub

Function getRange() As Range
    Compact
    Set getRange = Result
End Function

Addendum

Here was my first attempt at cracking 75 K areas. It performed very well with smaller number of unions but started to slow down exponentially after 20 K unions. Although, it probably isn't practical, there may be some merit to combining it with the FastUnion. If nothing else it was interesting to write.

StingUnion:Class

Option Explicit
Private Const MaxAddressSize As Long = 255
Private CurrentLength As Long
Private Result As Range
Private Parent As Worksheet
Private AddressHolder As String

Public Sub Add(Source As Range)
    If Parent Is Nothing Then
        Set Parent = Source.Parent
        AddressHolder = Space(MaxAddressSize)
    End If
    
    Dim length As Long
    Dim Address As String
    Address = Source.Address(0, 0)
    length = Len(Address)
    
    If (length + CurrentLength) > MaxAddressSize Then Compact
    
    If CurrentLength = 0 Then
        Mid(AddressHolder, CurrentLength + 1, length + 1) = Address
    Else
        Mid(AddressHolder, CurrentLength + 1, length + 1) = "," & Address
    End If
    CurrentLength = CurrentLength + length + 1
    
End Sub

Public Sub Compact()
    If CurrentLength = 0 Then Exit Sub
    
    If Result Is Nothing Then
        Set Result = Parent.Range(AddressHolder)
    Else
        Set Result = Union(Result, Parent.Range(AddressHolder))
    End If
    
    CurrentLength = 0
    AddressHolder = Space(MaxAddressSize)
End Sub

Function getRange() As Range
    Compact
    Set getRange = Result
End Function
added 266 characters in body
Source Link
TinMan
TinMan
  • 4.3k
  • 1
  • 13
  • 25
Option Explicit
Private Const MaxArgs As Long = 30
Private AllCells As Range
Private Groups(1 To MaxArgs) As Range
Private Index As Long
Private Count As Long
Private Compacted As Boolean

Public Sub Add(ByRef NewRange As Range)
    If Count < MaxArgs Then Count = Count + 1
    Index = Index + 1
    If Index > MaxArgs Then Index = IIf(Compacted, 2, 1)
    If Groups(Index) Is Nothing Then
        Set Groups(Index) = NewRange
    Else
        Set Groups(Index) = Union(Groups(Index), NewRange)
    End If
End Sub

Private Sub Compact()
    Select Case Count
        Case 1
            Set AllCells = Groups(1)
        Case 2
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2))
        Case 3
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3))
        Case 4
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4))
        Case 5
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5))
        Case 6
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6))
        Case 7
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7))
        Case 8
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8))
        Case 9
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9))
        Case 10
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10))
        Case 11
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11))
        Case 12
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12))
        Case 13
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13))
        Case 14
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14))
        Case 15
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15))
        Case 16
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16))
        Case 17
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17))
        Case 18
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18))
        Case 19
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19))
        Case 20
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20))
        Case 21
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21))
        Case 22
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22))
        Case 23
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23))
        Case 24
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24))
        Case 25
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25))
        Case 26
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26))
        Case 27
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27))
        Case 28
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28))
        Case 29
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28), Groups(29))
        Case 30
            Set AllCellsGroups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28), Groups(29), Groups(30))
    End Select
    
    Dim n As Long
    For n = 12 To MaxArgs
        Set Groups(n) = Nothing
    Next
    Index = 12
    Compacted = True
    Count = 0
    
End Sub

Public Function getRange() As Range
    Compact
    Set getRange = AllCellsGroups(1)
End Function

Edit

I modified the FastUnion class after I realized it would reset the range after Compact() was ran.

The OP pointed out I should list my specs.

System Specs

  • 64 bit Office 365
  • 6 GB Ram
  • 2.3 MHz processor
Option Explicit
Private Const MaxArgs As Long = 30
Private AllCells As Range
Private Groups(1 To MaxArgs) As Range
Private Index As Long
Private Count As Long

Public Sub Add(ByRef NewRange As Range)
    If Count < MaxArgs Then Count = Count + 1
    Index = Index + 1
    If Index > MaxArgs Then Index = 1
    If Groups(Index) Is Nothing Then
        Set Groups(Index) = NewRange
    Else
        Set Groups(Index) = Union(Groups(Index), NewRange)
    End If
End Sub

Private Sub Compact()
    Select Case Count
        Case 1
            Set AllCells = Groups(1)
        Case 2
            Set AllCells = Union(Groups(1), Groups(2))
        Case 3
            Set AllCells = Union(Groups(1), Groups(2), Groups(3))
        Case 4
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4))
        Case 5
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5))
        Case 6
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6))
        Case 7
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7))
        Case 8
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8))
        Case 9
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9))
        Case 10
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10))
        Case 11
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11))
        Case 12
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12))
        Case 13
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13))
        Case 14
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14))
        Case 15
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15))
        Case 16
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16))
        Case 17
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17))
        Case 18
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18))
        Case 19
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19))
        Case 20
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20))
        Case 21
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21))
        Case 22
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22))
        Case 23
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23))
        Case 24
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24))
        Case 25
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25))
        Case 26
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26))
        Case 27
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27))
        Case 28
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28))
        Case 29
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28), Groups(29))
        Case 30
            Set AllCells = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28), Groups(29), Groups(30))
    End Select
    
    Dim n As Long
    For n = 1 To MaxArgs
        Set Groups(n) = Nothing
    Next
    Index = 1
    Count = 0
    
End Sub

Public Function getRange() As Range
    Compact
    Set getRange = AllCells
End Function

Option Explicit
Private Const MaxArgs As Long = 30
Private Groups(1 To MaxArgs) As Range
Private Index As Long
Private Count As Long
Private Compacted As Boolean

Public Sub Add(ByRef NewRange As Range)
    If Count < MaxArgs Then Count = Count + 1
    Index = Index + 1
    If Index > MaxArgs Then Index = IIf(Compacted, 2, 1)
    If Groups(Index) Is Nothing Then
        Set Groups(Index) = NewRange
    Else
        Set Groups(Index) = Union(Groups(Index), NewRange)
    End If
End Sub

Private Sub Compact()
    Select Case Count
        Case 2
            Set Groups(1) = Union(Groups(1), Groups(2))
        Case 3
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3))
        Case 4
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4))
        Case 5
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5))
        Case 6
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6))
        Case 7
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7))
        Case 8
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8))
        Case 9
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9))
        Case 10
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10))
        Case 11
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11))
        Case 12
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12))
        Case 13
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13))
        Case 14
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14))
        Case 15
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15))
        Case 16
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16))
        Case 17
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17))
        Case 18
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18))
        Case 19
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19))
        Case 20
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20))
        Case 21
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21))
        Case 22
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22))
        Case 23
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23))
        Case 24
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24))
        Case 25
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25))
        Case 26
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26))
        Case 27
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27))
        Case 28
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28))
        Case 29
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28), Groups(29))
        Case 30
            Set Groups(1) = Union(Groups(1), Groups(2), Groups(3), Groups(4), Groups(5), Groups(6), Groups(7), Groups(8), Groups(9), Groups(10), Groups(11), Groups(12), Groups(13), Groups(14), Groups(15), Groups(16), Groups(17), Groups(18), Groups(19), Groups(20), Groups(21), Groups(22), Groups(23), Groups(24), Groups(25), Groups(26), Groups(27), Groups(28), Groups(29), Groups(30))
    End Select
    
    Dim n As Long
    For n = 2 To MaxArgs
        Set Groups(n) = Nothing
    Next
    Index = 2
    Compacted = True
    Count = 0
    
End Sub

Public Function getRange() As Range
    Compact
    Set getRange = Groups(1)
End Function

Edit

I modified the FastUnion class after I realized it would reset the range after Compact() was ran.

The OP pointed out I should list my specs.

System Specs

  • 64 bit Office 365
  • 6 GB Ram
  • 2.3 MHz processor
Source Link
TinMan
TinMan
  • 4.3k
  • 1
  • 13
  • 25