How to transpose multiple columns into a single column?

Question

In quantity, how can I transpose this format:

a      a1
b      b1
c      c1
d      d1
e      e1

into a single column as below?

a
a1
b
b1
c
c1
d
d1

Answer 1

You can also do this with VBA without having Excel select or activate anything. It's only coded to work on the active sheet right now, but it'd be very easy to make it work on any sheet that isn't actively selected.

Sub combineColumns()

        Dim sFirstCol As String
        Dim sSecondCol As String

        sFirstCol = "A"
        sSecondCol = "B"

        Dim values() As String
        Dim i As Integer
        Dim t As Integer
        Dim iFirstRow As Integer
        Dim iSecondRow As Integer
        iFirstRow = Range(sFirstCol & 1).End(xlDown).Row
        iSecondRow = Range(sSecondCol & 1).End(xlDown).Row

        ReDim values(iSecondRow - 1)

        For i = 0 To iFirstRow - 1
            values(i) = Range(sSecondCol & i + 1).Value
        Next i

        t = 0
        For i = 0 To iFirstRow * 2 - 1 Step 2
            Range(sFirstCol & i + 2).Insert
            Range(sFirstCol & i + 2).Value = values(t)
            t = t + 1
        Next i

        Range(sSecondCol & 1 & ":" & sSecondCol & iSecondRow * 2).ClearContents

 End Sub