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 YZTo 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 WXI'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

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

rowindexargument of the Cells method. "A3:A" is a string and the Cells method wants a number. Therefore the "Type" of theRowindexvariable 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

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

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:=xlPasteValuesI 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

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

Thanks a lot!

I still have much to learn :)

Ask Your Question

Weekly Poll

Do you think third-party cookies should be blocked by browsers?

Discuss in The Lounge

Poll History