Stack multiple columns VBA Code

[rathalex]

Hello, I have come across an issue stacking multiple columns in excel 2007. I have 9 columns that need to be stacked up to last non empty cell. The data changes in every column occasionally, so range of each column might be different. There i do need to include Range.End(xlUp) anyhow.... Ive tried range.resize option,but cant seem to get it still. Only was able to stack 2 columns.Any suggestions? Im still new to VBA,so its probably something very simple.

 

[My Aswer Is This]

You want to take columns 2 to 8 and paste them all into column (1) is that correct?

 

[My Aswer Is This]

If the answer to my question is yes then try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
    For i = 2 To 8
        Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
        Lastrowa = Cells(Rows.Count, i).End(xlUp).Row
        Range(Cells(1, i), Cells(Lastrowa, i)).Copy Cells(Lastrow, 1)
    Next
Application.ScreenUpdating = True
End Sub

 

[rathalex]

Thank You My Aswer! The columns I have spread apart pretty far...I need to take data from columns D,BP,BS,BV,BY,CB,CE,CH,CK starting from cell 8 in each and up to last nonempty cell down the bottom. Then stack up them all in column CN starting from CN8 as well.

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
    For i = 1 To 9
        Lastrow = Cells(Rows.Count, Choose(i, "D", "BP", "BS", "BV", "BY", "CB", "CE", "CH", "CK")).End(xlUp).Row + 1
        If i = 1 Then
            Lastrowa = 8
        Else
        Lastrowa = Cells(Rows.Count, "CN").End(xlUp).Row 'mod
        End If
        Range(Cells(8, Choose(i, "D", "BP", "BS", "BV", "BY", "CB", "CE", "CH", "CK")), Cells(Lastrow, Choose(i, "D", "BP", "BS", "BV", "BY", "CB", "CE", "CH", "CK"))).Copy Cells(Lastrowa + 1, "CN")
    Next
Application.ScreenUpdating = True
End Sub

 

[rathalex]

Worked beautifully! Thank you very much for the help!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Worked beautifully! Thank you very much for the help!

 

[rathalex]

Always here! Will be back for more for sure!

 

[bperrow]

Hi. My Aswer Is THis - i also have an issue with Stacking columns - i have tried using your first post above and have got it working for i = 5 to 6, which grabs columns E and F as i want it to but it stacks them in to column A in the same sheet. I need to stack them in to Column A in a different sheet i have tried a few things but i cant quite get it to work - any help would be appreciated. TIA

 

[My Aswer Is This]

Hi. My Aswer Is THis - i also have an issue with Stacking columns - i have tried using your first post above and have got it working for i = 5 to 6, which grabs columns E and F as i want it to but it stacks them in to column A in the same sheet. I need to stack them in to Column A in a different sheet i have tried a few things but i cant quite get it to work - any help would be appreciated. TIA

I would need to know both sheet names.
Like copy columns C and D from sheet named "Alpha" and paste into column A of sheet named "Bravo"