Converting a table of cell values to a row

[glad_ir]

Hello,

Please could somebody show me how to code VBA to copy table values into a single row using VBA - e.g. copy A2:C4 into row E1 to E9 as shown below

I have a number of larger tables to do this with so am looking for some code for the below I can modify to handle these.

Any help much appreciated.

Thank you
Iain

Cell to be copied	Destination
A2	E1
A3	E2
A4	E3
B2	E4
B3	E5
B4	E6
C2	E7
C3	E8
C4	E9

 

[Norie]

If it's only the values you want try this - adjust the range to suit and change 2 to 1 on the first loop if there are no headers.

Sub TableToCol()
Dim arrIn As Variant
Dim arrOut As Variant
Dim cnt As Long
Dim idxCol As Long
Dim idxRow As Long

    arrIn = Range("A1").CurrentRegion.Value

    ReDim arrOut(1 To UBound(arrIn, 1) * UBound(arrIn, 2), 1 To 1)

    For idxRow = 2 To UBound(arrIn, 1)
        For idxCol = 1 To UBound(arrIn, 2)
            cnt = cnt + 1
            arrOut(cnt, 1) = arrIn(idxRow, idxCol)
        Next idxCol
    Next idxRow

    Range("E1").Resize(cnt).Value = arrOut

End Sub

 

[glad_ir]

If it's only the values you want try this - adjust the range to suit and change 2 to 1 on the first loop if there are no headers.

Sub TableToCol()
Dim arrIn As Variant
Dim arrOut As Variant
Dim cnt As Long
Dim idxCol As Long
Dim idxRow As Long

    arrIn = Range("A1").CurrentRegion.Value

    ReDim arrOut(1 To UBound(arrIn, 1) * UBound(arrIn, 2), 1 To 1)

    For idxRow = 2 To UBound(arrIn, 1)
        For idxCol = 1 To UBound(arrIn, 2)
            cnt = cnt + 1
            arrOut(cnt, 1) = arrIn(idxRow, idxCol)
        Next idxCol
    Next idxRow

    Range("E1").Resize(cnt).Value = arrOut

End Sub

Hi, thank you for the prompt reply and your help - I really appreciate it.

The code works a treat for what I asked........but I asked the wrong question. I'm sorry, I need to convert to a row rather than a column. Please could you advise changes to the code for this?

Sorry,
Iain

Cell to be copied	Destination
A2	E1
A3	F1
A4	G1
B2	H1
B3	I1
B4	J1
C2	K1
C3	L1
C4	M1

 

[Norie]

Oops, misread your post - think the way the layout of the posted data was confusing too.

Nevermind easily fixed.

Sub TableToRow()
Dim arrIn As Variant
Dim arrOut As Variant
Dim cnt As Long
Dim idxCol As Long
Dim idxRow As Long

    arrIn = Range("A1").CurrentRegion.Value

    ReDim arrOut(1 To 1, 1 To UBound(arrIn, 1) * UBound(arrIn, 2))

    For idxRow = 2 To UBound(arrIn, 1)
        For idxCol = 1 To UBound(arrIn, 2)
            cnt = cnt + 1
            arrOut(1, cnt) = arrIn(idxRow, idxCol)
        Next idxCol
    Next idxRow

    Range("E1").Resize(, cnt).Value = arrOut

End Sub

 

[glad_ir]

Oops, misread your post - think the way the layout of the posted data was confusing too.

Nevermind easily fixed.

Sub TableToRow()
Dim arrIn As Variant
Dim arrOut As Variant
Dim cnt As Long
Dim idxCol As Long
Dim idxRow As Long

    arrIn = Range("A1").CurrentRegion.Value

    ReDim arrOut(1 To 1, 1 To UBound(arrIn, 1) * UBound(arrIn, 2))

    For idxRow = 2 To UBound(arrIn, 1)
        For idxCol = 1 To UBound(arrIn, 2)
            cnt = cnt + 1
            arrOut(1, cnt) = arrIn(idxRow, idxCol)
        Next idxCol
    Next idxRow

    Range("E1").Resize(, cnt).Value = arrOut

End Sub

All my fault. Thank you so much for this - it will really help me avoid some really mundane work!

cheers,
Iain