Basically, you need to loop each area and put the value into an output
array that you can dump back into your sheet to a row or col as
desired.

Try...

Sub CopyAreas()
Dim vAreas, vData, n&, j&, sVals$
vAreas = Split(Selection.Address, ",")
For n = LBound(vAreas) To UBound(vAreas)
vData = Range(vAreas(n))
If Not IsArray(vData) Then '//single cell
sVals = sVals & "~" & vData
Else
For j = LBound(vData) To UBound(vData)
sVals = sVals & "~" & vData(j, 1)
Next 'j
End If 'Not IsArray(vData)
Next 'n
vData = Split(Mid(sVals, 2), "~")
'Resize the target range and dump the data
'To col
Range("M1").Resize(UBound(vData) + 1, 1) = _
Application.Transpose(vData)
'To row
Range("M1").Resize(1, UBound(vData) + 1) = vData
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion