Solved VBA to Stack The Data in Two Columns

Microsoft Excel 2010 - complete product...
October 8, 2018 at 07:37:57
Specs: Windows 7 x64, 2,4 GHz / 4 GB
I have 300+ rows of data in each of columns A and B and I would like to stack them, starting from row 3.

As an example I would like this:

        A      B
3      AB      CD
4      EF      GH
5      IJ      KL
6      MN      OP
7      QR      ST
8      UV      WX
9      YZ

To become this:

       C
3      AB
4      EF
5      IJ
6      MN
7      QR
8      UV
9      YZ
10     CD
11     GH
12     KL
13     OP
14     ST
15     WX

I'm clearly doing something wrong in VBA:

Dim LastrowA As Long
Dim LastrowB As Long
Dim LastrowC As Long

LastrowA = Cells(Rows.Count, "A").End(xlUp).Row
LastrowB = Cells(Rows.Count, "B").End(xlUp).Row

Range(Cells("A3:A" & LastrowA, "A")).Copy Range(3, "C")

LastrowC = Cells(Rows.Count, "C").End(xlUp).Row

Range(Cells("B3:B" & LastrowB, "B")).Copy Range(LastrowC + 1, "C")

message edited by Mrrrr


See More: VBA to Stack The Data in Two Columns

Reply ↓  Report •

#1
October 8, 2018 at 12:51:28
You are mixing up and combining syntaxes. This is not a valid Range reference:

Range(Cells("A3:A" & LastrowA, "A"))

You are probably getting a Type Mismatch error because VBA is expecting a numeric value for the rowindex argument of the Cells method. "A3:A" is a string and the Cells method wants a number. Therefore the "Type" of the Rowindex variable doesn't "match" what it it supposed to be.

And then there's this:

Range(3, "C")

Again, a mix and match. It has to be either this...

Range("C3")

..or this:

Cells(3, "C")

'Twere it me, I'd use:

Range("A3:A" & LastrowA).Copy Range("C3")

and

Range("B3:B" & LastrowB).Copy Range("C" & LastrowC + 1)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 8, 2018 at 16:56:18
✔ Best Answer
BTW...This can be done with a formula, although determining where to stop dragging the formula is kind of manual.

Put this in C3 and drag it down as far as you think you'll need and then keep going ;-). It's OK to drag it down too far because you'll just get blank cells. Obviously, if you don't drag it down far enough, you'll miss some values.

=IF(ROW()>SUM(COUNTA(A:A),COUNTA(B:B),2),"",IF(A3<>"",A3,INDIRECT("B"&ROW()-COUNTA(A:A))))

Basically, you must drag it down to at least 2 rows beyond the number of values in Column A plus the number of values in Column B. In your example, you have 7 + 6 = 13 values, so you must drag it down to at least Row 15 to get the entire list.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#3
October 11, 2018 at 22:19:37
I figured out what I was doing wrong and made some code snippet for each set of 2 columns in part (i had 8 columns to stack, 2 by 2), like:

Range("C2").Select
Range("A2:A" & Lastrow).Copy
    ActiveCell.PasteSpecial Paste:=xlPasteValues
FirstFree = Range("C2:C" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("C" & FirstFree).Select
Range("B2:B" & Lastrow).Copy
    ActiveCell.PasteSpecial Paste:=xlPasteValues

I had found a formula too but it required both columns to be the same number of rows and start on the same row and you needed helper columns too. I had data in columns A3:A9 and B3:B9, 3 helper columns and in column G the result: http://i.imgur.com/XC3GAGU.png

It's a bit of a stretch really. Thanks for the solutions!

message edited by Mrrrr


Reply ↓  Report •

Related Solutions

#4
October 12, 2018 at 05:44:08
You are making life too hard on yourself (and VBA).

There is no need to .Select a cell to use it as the Paste location. There is rarely a need to .Select a cell in VBA when performing an operation on it.

Two instructions are all that are needed:

Sub StackEm()

  Range("A3:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy _
     Range("C3")

  Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy _
     Range("C" & Cells(Rows.Count, "C").End(xlUp).Row + 1)

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question