Firstly, if you apply the function TRANSPOSE() to result of function ArrayUnion(), you will not get the column headings as intended, but one column of values. Will ArrayUnion() return simple array? So it will be single ROW of values.
Code: Select all
Function ArrayUnion(va1 As Variant, _
Optional va2 As Variant, _
Optional va3 As Variant, _
Optional va4 As Variant, _
Optional va5 As Variant, _
Optional va6 As Variant, _
Optional va7 As Variant, _
Optional va8 As Variant, _
Optional va9 As Variant, _
Optional va10 As Variant, _
Optional va11 As Variant, _
Optional va12 As Variant) As Variant
Dim lastElement As Long
Dim res() As Variant
lastElement = -1
addParamToArray(va1, res, lastElement)
If Not IsMissing(va2) Then addParamToArray(va2, res, lastElement)
If Not IsMissing(va3) Then addParamToArray(va3, res, lastElement)
If Not IsMissing(va4) Then addParamToArray(va4, res, lastElement)
If Not IsMissing(va5) Then addParamToArray(va5, res, lastElement)
If Not IsMissing(va6) Then addParamToArray(va6, res, lastElement)
If Not IsMissing(va7) Then addParamToArray(va7, res, lastElement)
If Not IsMissing(va8) Then addParamToArray(va8, res, lastElement)
If Not IsMissing(va9) Then addParamToArray(va9, res, lastElement)
If Not IsMissing(va10) Then addParamToArray(va10, res, lastElement)
If Not IsMissing(va11) Then addParamToArray(va11, res, lastElement)
If Not IsMissing(va12) Then addParamToArray(va12, res, lastElement)
If lastElement > 0 Then
ReDim Preserve res(lastElement)
Else
res = Array()
EndIf
ArrayUnion = res
End Function
Sub addParamToArray(param As Variant, res As Variant, lastElement As Long)
Const NEW_SIZE = 10
Dim i As Long, j As Long, lastSize As Long
If IsArray(param) Then
For i = LBound(param) To UBound(param)
For j = LBound(param, 2) To UBound(param, 2)
lastElement = lastElement + 1
If lastElement > UBound(res) Then
lastSize = UBound(res) + NEW_SIZE
ReDim Preserve res(lastSize)
EndIf
res(lastElement) = param(i, j)
Next j
Next i
Else
lastElement = lastElement + 1
If lastElement > UBound(res) Then
lastSize = UBound(res) + NEW_SIZE
ReDim Preserve res(lastSize)
EndIf
res(lastElement) = param
EndIf
End Sub
You can call it like as
Code: Select all
{=TRANSPOSE(ARRAYUNION(A1:A18;E1;C1:C7))}
{=ARRAYUNION(A1:A18;C1:C7)}
{=ARRAYUNION("Result:";C1:C7;C1:C7;C1:C7;C1:C7;C1:C7;C1:C7;C1:C7;C1:C7;C1:C7)} etc
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English