CODEWITHSUNDEEP
excelvba
wonders of nature
wonders of nature

Reputation: 83

Excel - Mix multiple columns into one column

I have two columns

          Column 1 Column 2
row 1:       A        1
row 2:       B        2
row 3:       C        3

I want to mix column like this

          Column 1 
row 1:       A      
row 2:       1        
row 3:       B
row 4:       2
row 5:       C
row 6:       3

I tried many examples but none of them worked.I am newbie to vba. Please help I tried one below from another stack overflow example.but it will add only on last cell off column.I don't know how to change it.

Sub CombineColumns()
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer

Set rng = ActiveCell.CurrentRegion
lastCell = rng.Columns(1).Rows.Count + 1

For iCol = 2 To rng.Columns.Count
    Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
    ActiveSheet.Paste Destination:=Cells(lastCell, 1)
    lastCell = lastCell + rng.Columns(iCol).Rows.Count
Next iCol
End Sub

Upvotes: 1

Views: 1538

Answers (4)

Sumit Jain
Sumit Jain

Reputation: 377

You can use this formula and drag it down. 

=OFFSET($A$1,(EVEN(ROW(A6))-2)/2,MOD(ROW(A6)-1,2))

where A1 is your 1st cell. In the above example location of "A"

Upvotes: 2

Eduardo
Eduardo

Reputation: 2514

While Ros answer is quite elegant, in the sense it does not use VBA, I leave the answer using VBA, since the question is regarding VBA.

The following code assumes column 1 and 2 are columns A and B of the worksheet and that the ActiveCell is Cell A1. The output is on column C.

Sub CombineColumns()
    Dim rng As Range
    Dim iCell As Integer

    Set rng = ActiveCell.CurrentRegion

    Dim iCell As Integer
    For iCell = 1 To rng.Cells.Count
        Range("C1").Offset(iCell - 1, 0) = rng.Item(iCell)
    Next iCell

End Sub

Upvotes: 2

manu
manu

Reputation: 942

You can do it like so with VBA:

Option Explicit
Sub Mix()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim i As Long, j As Long, Rng1 As Range, Rng2 As Range
Dim Lastrow As Long, Arr1() As Variant, Arr2() As Variant
Dim n As Long, a As Long

With ws

Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
Set Rng1 = .Range(.Cells(1, "A"), .Cells(Lastrow, "A"))
Set Rng2 = .Range(.Cells(1, "B"), .Cells(Lastrow, "B"))

Arr1 = Rng1.Value2
Arr2 = Rng2.Value2

n = 1
For i = LBound(Arr1, 1) To UBound(Arr1, 1)
    .Cells(n, "A") = Arr1(i, 1)
    n = n + 2
Next i

a = 2
For j = LBound(Arr2, 1) To UBound(Arr2, 1)
    .Cells(a, "A") = Arr2(j, 1)
a = a + 2
Next j

End With

End Sub

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

You don't really need VBA for this. You can use a formula:

=IFERROR(INDEX($A$1:$B$3,INT((ROWS($1:1)-1)/2)+1,MOD(ROWS($1:1)-1,2)+1),"")

Adjust the array_range to what your data really is, and fill down.

Upvotes: 4

Related Questions