Stacking Columns

rjbinney

Board Regular
Joined
Dec 20, 2010
Messages
231
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,974
Try:
Code:
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
 

Robertmwaring

New Member
Joined
Mar 8, 2019
Messages
15
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,974
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,003
Messages
5,465,948
Members
406,454
Latest member
d87

This Week's Hot Topics

Top