Stacking Columns

[rjbinney]

My Google skills have found ways to combine multiple columns into ONE column; I'm trying to figure out how to do TWO columns.

Basically, I have received data from a survey.
Every other column is the Question on the survey, then the next column is the Answer. (i.e., |Q1|A1|Q2|A2|Qn|An|). Each row is a new respondent's set of answers. Sixty-seven questions, 140 respondents.

I'd like column A to be a stack of all questions, and B to be all responses. (That is, rows A1-A140 is Q1, B1-B140 are all the answers to Q2, A141-A280 is Q2, B141-B280 a list of all the answers, and so on).

Seems easy enough, but...?

Thanks

 

[mumps]

Try:

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, lCol As Long, x As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    For x = 3 To lCol Step 2
        Cells(1, x).Resize(LastRow, 2).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next x
    Range(Cells(1, 3), Cells(1, lCol)).EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

 

[rjbinney]

Effin' brilliant. Worked like a charm out of the gate.

Thank you.

 

[mumps]

Your are very welcome.

 

[robertmwaring2]

I got excited when I read this post as it comes closest to the same thing I am trying to accomplish. I have little to no knowledge of VBA so I don't really understand the code to determine what I need to alter - while its similar to writing formulas in cells, it is simultaneously different.
Any chance you'd be willing to explain each step in the code? Im trying to stack 4 column ranges (AEI:AEZ,AFB:AFS,AFU:AGL,AGN:AHE) into 4 separate columns (AHK,AHM,AHO,AHQ) respectively, and sequentially (meaning AEI, then AEJ, then AEK and so on - the same for each of the ranges) without coping blank cells. The information in the 4 column ranges populates once I click a button on a userform and was hoping to tie it in with that sequence of events. I have tried countless codes I acquired online through various pages but I'm either altering them wrong, its not what I need, or Im placing them in the wrong area - hence the reason for trying to understand what you've provided so I can possibly figure out how to accomplish what I'm looking to do. Thanks in advance for your help.

 

[mumps]

According to forum rules you should start your own thread. Please do that and send me a private message with a link to your thread and I'll have a look at it.

 

[robertmwaring2]

My apologies sir, I appreciate your response in any case. Thank you for the warning.