Replicate Google Sheets {array1; array2} function in Microsoft Excel as user defined function

Question

In Google Sheets, I can use the ={range1;range2;...} notation to append multiple ranges into one as part of a formula. In Excel, it seems this functionality isn't available as a formula. I would like to create a user defined function to allow me to append ranges in Excel.

Ideally, the function would have a similar simplicity to the Google Sheets version. I've attached a link to a public Google Sheets document with a basic use case, in case it's helpful.

I've tried Tom's answer but it gives an error (see example photo).

Thanks!

Answer 1

Here is a general UDF to stack ranges of the same number of columns:

Function vStack(ParamArray rng() As Variant) As Variant
    If TypeName(rng(1)) <> "Range" Then Exit Function
    Dim otarr() As Variant
    ReDim otarr(1 To 100000, 1 To rng(1).Columns.Count)
    
    Dim z As Long
    z = 1
    
    Dim i As Long
    For i = LBound(rng) To UBound(rng)
        If TypeName(rng(i)) <> "Range" Then Exit Function
        If i > LBound(rng) Then
            If rng(i).Columns.Count <> rng(i - 1).Columns.Count Then Exit Function
        End If
        Dim rngarr As Variant
        rngarr = Intersect(rng(i), rng(i).Parent.UsedRange)
        Dim j As Long
        For j = LBound(rngarr, 1) To UBound(rngarr, 1)
            Dim k As Long
            For k = LBound(rngarr, 2) To UBound(rngarr, 2)
                otarr(z, k) = rngarr(j, k)
            Next k
            z = z + 1
        Next j
    Next i
    
    Dim nArray() As Variant
    ReDim nArray(1 To z - 1, 1 To UBound(otarr, 2))
    
    For i = 1 To z - 1
        For j = 1 To UBound(otarr, 2)
            nArray(i, j) = otarr(i, j)
        Next j
    Next i
    
    vStack = nArray

End Function

One note, I limit the initial array to 100,000 rows. If this is not enough you can up that to what ever you want, but also think, "Am I treating Excel as a database?". If the answer is yes, it is time to make the switch to an actual referential database.

Then one can use it in a formula:

=FILTER(vStack(A:C,F:H),vStack(A:A,F:F)="Apples")

---

Edit to include a version that works with arrays ie: =vstack({1;2;3},{4;5;6})

Function vStack(ParamArray rng() As Variant) As Variant
    Dim otarr() As Variant
    If TypeName(rng(1)) = "Range" Then
        ReDim otarr(1 To 100000, 1 To rng(1).Columns.Count)
    Else
        ReDim otarr(1 To 100000, 1 To UBound(rng(1), 2))
    End If
        
    
    
    Dim z As Long
    z = 1
    
    Dim i As Long
    For i = LBound(rng) To UBound(rng)
        
        If i > LBound(rng) Then
            If TypeName(rng(i)) = "Range" Then
                If rng(i).Columns.Count <> UBound(otarr, 2) Then Exit Function
            Else
                If UBound(rng(i), 2) <> UBound(otarr, 2) Then Exit Function
            End If
        End If
        Dim rngarr As Variant
        If TypeName(rng(i)) = "Range" Then
            rngarr = Intersect(rng(i), rng(i).Parent.UsedRange)
        Else
            rngarr = rng(i)
        End If

        Dim j As Long
        For j = LBound(rngarr, 1) To UBound(rngarr, 1)
            Dim k As Long
            For k = LBound(rngarr, 2) To UBound(rngarr, 2)
                otarr(z, k) = rngarr(j, k)
            Next k
            z = z + 1
        Next j
    Next i
    
    Dim nArray() As Variant
    ReDim nArray(1 To z - 1, 1 To UBound(otarr, 2))
    
    For i = 1 To z - 1
        For j = 1 To UBound(otarr, 2)
            nArray(i, j) = otarr(i, j)
        Next j
    Next i
    
    vStack = nArray

End Function

Answer 2

UPDATED With Dynamic Available Ranges

This function should create what you want. Note this does not take into account blanks or headers. See sample spreadsheet with working result.

Function combineRange(ParamArray theRanges() As Variant) As Variant()
    Dim totalColumns As Long, z As Long, r As Long, g As Long, aCell As Range
    
    ReDim zRanges(0) As Range
    For r = LBound(theRanges) To UBound(theRanges)
        ReDim Preserve zRanges(r)
        Set zRanges(r) = theRanges(r)
        Set zRanges(r) = Intersect(zRanges(r), zRanges(r).Worksheet.UsedRange)
       
        totalColumns = Application.WorksheetFunction.Max(zRanges(r).Columns.Count, totalColumns)
    Next r
    
    r = 1
    
    ReDim theRay(1 To totalColumns, 1 To r)
    
    For z = LBound(zRanges) To UBound(zRanges)
        For Each aCell In zRanges(z).Columns(1).Cells
           ReDim Preserve theRay(1 To totalColumns, 1 To r)
            For g = 1 To zRanges(z).Columns.Count
                theRay(g, r) = aCell.Offset(0, g - 1).Value
            Next g
            r = r + 1
        Next aCell
    Next z
    
    combineRange = Application.WorksheetFunction.Transpose(theRay)
End Function