Union of multiple ranges

Let's say, (7, 10) and (11, 13) result into (7, 13):

a = [(7, 10), (11, 13), (11, 15), (14, 20), (23, 39)]
b = []
for begin,end in sorted(a):
    if b and b[-1][1] >= begin - 1:
        b[-1] = (b[-1][0], end)
    else:
        b.append((begin, end))

b is now

[(7, 20), (23, 39)]

EDIT:

As @CentAu correctly notices, [(2,4), (1,6)] would return (1,4) instead of (1,6). Here is the new version with correct handling of this case:

a = [(7, 10), (11, 13), (11, 15), (14, 20), (23, 39)]
b = []
for begin,end in sorted(a):
    if b and b[-1][1] >= begin - 1:
        b[-1][1] = max(b[-1][1], end)
    else:
        b.append([begin, end])

Get Outer Bounding Range of Union with Multiple Areas

This function uses the Application.Range property (Excel) to create the Range Around the Union Range.

Function UnionRange_ƒRangeAround_Set(rUnion As Range) As Range
Dim rOutput As Range, b As Byte
        
    With rUnion
        Set rOutput = .Areas(1)
        For b = 2 To .Areas.Count
            Set rOutput = Range(rOutput, .Areas(b))
        Next
    End With

    Set UnionRange_ƒRangeAround_Set = rOutput
    
    End Function

How to union ranges in google spreadsheets

Google Apps Script

And yet the question was about the script. I'm still successfully using the following code:

function unionRanges(e) {
  var result = [];
  var length = 0;
  var i = 0;
  try {
    for (i = 0; i < arguments.length; i++)
      length += arguments[i].length;
    if (length > 3000) return '#BIGRANGE';
    for (var i = 0; i < arguments.length; i++)
      result = result.concat(arguments[i].filter(function (el) {
        return el.join('').length > 0
      }));
    return result;
  } catch (err) {
    return JSON.stringify(err);
  }
}

Spreadsheets feature

But, as noted above, it is easier to use {}-notation.

={ Sheet1!A1:C10 ; Sheet2!A1:C34 }

Vertical concatenation

={ Range(Cols=N) ; Range(Cols=N) }

Horizontal concatenation

={ Range(Rows=M) , Range(Rows=M) }

It's possible to combine

={ { , , } ; { , , } }

Or something more hard

={{{;;},{;;}};{{;;},{;;}};{{;;},{;;}}}

Try something like this

={
   {{ 1; 2; 3},{ 4; 5; 6}};
   {{ 7; 8; 9},{10;11;12}};
   {{13;14;15},{16;17;18}}
 }

The internal horizontal concatenation is not required

={
    { 1; 2; 3},{ 4; 5; 6};
    { 7; 8; 9},{10;11;12};
    {13;14;15},{16;17;18}
}

Locale dependencies of argument delimiters

If your current locale supports , as an argument delimiter thnen you should use ; for a vertical concatenation and , for a horizontal concatenation.

Otherwise your argument delimiter is ; and you have to use ; and \ (without spaces), respectively.

Sheet 'Data 1'!A1:C20

|   Name  |    Date   | Sum |
| Ethan   |  3/4/2017 |  31 |
| Logan   |  3/6/2017 |  62 |
| Brian   | 3/26/2017 |  61 |
|   ...   |     ...   | ... |

Sheet 'Data 2'!A1:C20

|  Name   |    Date   | Sum |
| Nathan  | 3/30/2017 |  53 |
| Alyssa  | 3/13/2017 |  72 |
| John    | 3/24/2017 |  79 |
| Megan   | 3/16/2017 |  10 |
|   ...   |     ...   | ... |

Concatenation

Vertical concatenation

={'Data 1'!A1:C20;'Data 2'!A2:C20}

Result

|  Name  |    Date   | Sum |
| Ethan  |  3/4/2017 |  31 |
| Logan  |  3/6/2017 |  62 |
| Brian  | 3/26/2017 |  61 |
| ...    |       ... | ... |
| Nathan | 3/30/2017 |  53 |
| Alyssa | 3/13/2017 |  72 |
| John   | 3/24/2017 |  79 |
| ...    |       ... | ... |

Horizontal concatenation

={TRANSPOSE('Data 1'!A1:C20),TRANSPOSE('Data 2'!A2:C20)}

Result

| Name |   Ethan  |   Logan  |   Brian   | ... |   Nathan  |   Alyssa  |    John   |
| Date | 3/4/2017 | 3/6/2017 | 3/26/2017 | ... | 3/30/2017 | 3/13/2017 | 3/24/2017 |
| Sum  |       31 |       62 |        61 | ... |        53 |        72 |        79 |

More about this How to concatenate ranges in Google spreadsheets

How to combine two non-adjacent ranges using Union?

Succeeded by defining the range as a collection and by using a function that transforms the collection to an array. See code below:

Sub combineRange()

Dim CombinedRange As Collection
Set CombinedRange = New Collection

CombinedRange.Add ActiveSheet.Range("A1:E1")
CombinedRange.Add ActiveSheet.Range("A3:E3")

'transfer cominedRange to array using function CollectionToArray
varTable = CollectionToArray(CombinedRange)

End Sub

 Function CollectionToArray(col As Collection) As Variant()
    Dim arr() As Variant, index As Long, it As Variant
    ReDim arr(col.Count - 1) As Variant
    For Each it In col
        arr(index) = it
        index = index + 1
    Next it
    CollectionToArray = arr
End Function

---

Related Topics