This function will transpose an array or range in Excel to a target cell with then help of vba. Transponation can be performed both horizontally and vertically.

Option Explicit
Option Base 0

Public Function Transpose(TargetCell As Range, Vertically As Boolean, Optional SourceRange As Range, Optional SourceArray As Variant) As Range
' ***************************************
' Transposes an array or range to a predefined target cell, either horizontally or vertically
' ***************************************
' Copyright ┬ę 2012 De Zinger ICT
' ***************************************
' Parameters:
' TargetCell:   Cell where the transponation should take place
' Vertically:   True if transponation should occur vertically, False if horizontally
' SourceRange:  If filled out, range with transponation data
' SourceArray:  If filled out, array with transponation data
' Return:       Targeted range
' ***************************************
' Usage:
' For range:
'           Add some data in cells a15:g15
'           In direct window: ? Transpose (Worksheets(1).Range("A1"), True, Worksheets(1).Range("A15:G15")).Address
' For array:
'           In direct window: ? Transpose (Worksheets(1).Range("A1"), True, , Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)).Address
' ***************************************
Dim varArray()
   
    If Not SourceRange Is Nothing Then
        SourceArray = SourceRange
    End If
   
    If Vertically = True Then
        'Vertically
       If Not SourceRange Is Nothing Then
            Set Transpose = Worksheets(1).Range(TargetCell.Address & ":" & Application.ConvertFormula("R" & UBound(SourceArray, 2) & "C" & TargetCell.Column, xlR1C1, xlA1))
        ElseIf Not IsMissing(SourceArray) Then
            Set Transpose = Worksheets(1).Range(TargetCell.Address & ":" & Application.ConvertFormula("R" & UBound(SourceArray) + 1 & "C" & TargetCell.Column, xlR1C1, xlA1))
        End If
        Transpose.Value = Application.WorksheetFunction.Transpose(SourceArray)
    Else
        'Horizontally
       If Not SourceRange Is Nothing Then
            Set Transpose = Worksheets(1).Range(TargetCell.Address & ":" & Application.ConvertFormula("R" & TargetCell.Row & "C" & UBound(SourceArray, 2), xlR1C1, xlA1))
        ElseIf Not IsMissing(SourceArray) Then
            Set Transpose = Worksheets(1).Range(TargetCell.Address & ":" & Application.ConvertFormula("R" & TargetCell.Row & "C" & UBound(SourceArray) + 1, xlR1C1, xlA1))
        End If
        Transpose.Value = SourceArray
    End If

End Function