Stack without VStack

[topi1]

Hi,
I am using the following code to stack multiple columns into one. Why is the macro getting stuck in the Second last line "Next"? I use Excel 2010.
Thank you.

Sub Stack_New()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
    For i = 2 To 800
        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

 

[6StringJazzer]

Can you be more specific about what "getting stuck" means here? What are you expecting this Sub to do, and what is it doing instead? Are you getting an error? Is the code hanging?

The only thing I notice here is that if a column is blank, Lastrowa will be 1 and you will still waste time copying blank cells. Is that what is intended?

 

[Eric W]

I tried your macro with some sample data, and it worked for me. I suspect doing so many copies in a loop might be the problem though, I only did 19 columns. Do you need to copy the cell formatting though? If you just want to stack the values, then this macro will be much faster, and I suspect won't have your problem with getting stuck. I assume you'll have a value in A1.

Sub Stack_New2()
Dim ur As Variant, r As Long, c As Long, c2 As Long, op() As Variant

    ur = ActiveSheet.UsedRange.Value
    ReDim op(1 To UBound(ur) * 800, 1 To 1)
    c2 = 0
    For c = 1 To IIf(UBound(ur, 2) < 800, UBound(ur, 2), 800)
        For r = 1 To UBound(ur)
            If ur(r, c) = "" Then Exit For
            c2 = c2 + 1
            op(c2, 1) = ur(r, c)
        Next r
    Next c
    
    Range("A1").Resize(c2).Value = op
    
End Sub

 

[topi1]

Can you be more specific about what "getting stuck" means here? What are you expecting this Sub to do, and what is it doing instead? Are you getting an error? Is the code hanging?

The only thing I notice here is that if a column is blank, Lastrowa will be 1 and you will still waste time copying blank cells. Is that what is intended?

Sorry, I should have been clearer. I am trying to stack rows C and onwards into the Row A. The number of columns I need to include to the right of C will increase as months pass by. My best guess is that there won't be any empty cells between filled cells in any of the rows. Each row will have variable number of contiguous data. I think there wont be any empty columns between filled columns. I ran Eric W's code and it worked great. Thank you for your follow up.

 

[topi1]

I tried your macro with some sample data, and it worked for me. I suspect doing so many copies in a loop might be the problem though, I only did 19 columns. Do you need to copy the cell formatting though? If you just want to stack the values, then this macro will be much faster, and I suspect won't have your problem with getting stuck. I assume you'll have a value in A1.

Sub Stack_New2()
Dim ur As Variant, r As Long, c As Long, c2 As Long, op() As Variant

    ur = ActiveSheet.UsedRange.Value
    ReDim op(1 To UBound(ur) * 800, 1 To 1)
    c2 = 0
    For c = 1 To IIf(UBound(ur, 2) < 800, UBound(ur, 2), 800)
        For r = 1 To UBound(ur)
            If ur(r, c) = "" Then Exit For
            c2 = c2 + 1
            op(c2, 1) = ur(r, c)
        Next r
    Next c
   
    Range("A1").Resize(c2).Value = op
   
End Sub

Thank you @Eric W. It did the trick. Appreciate it!

 

[Eric W]

Glad we could help.