Hello,

I suggest to take this UDF:
Function copy_row_wise(r As Range)
'Copy each element in r row-wise to selected area.
'PB V0.9
Dim ri As Range
Dim i As Long, j As Long
Dim vR As Variant

If TypeName(Application.Caller) < "Range" Then
copy_row_wise = CVErr(xlErrRef)
Exit Function
End If

If Application.Caller.Rows.Count * Application.Caller.Columns.Count <
_
r.Rows.Count * r.Columns.Count Then
copy_row_wise = CVErr(xlErrNum)
Exit Function
End If

ReDim vR(1 To Application.Caller.Rows.Count, _
1 To Application.Caller.Columns.Count)

i = 1
j = 1
For Each ri In r
If IsEmpty(ri) Then
vR(i, j) = ""
Else
vR(i, j) = ri
End If
j = j + 1
If j Application.Caller.Columns.Count Then
j = 1
i = i + 1
End If
Next ri

copy_row_wise = vR

End Function

HTH,
Bernd