locked
combine multiple columns in one column via VBA RRS feed

  • Question

  • Hi there,

    I want to combine multiple columns in one column.

    I have 1371 columns (C1 C2 C3 ....C1370) and 214 rows in each column in an excel sheet. I have blank cells as well in columns and I want those as well to make it a panel data.

    I want to put these columns together in one column like so after 214 rows of C1 , it shows 214 rows of C2, then 214 rows of C3,.... C1371.

    so once its done, in total C1 should contain 293180 rows. 

    I am using the following macro and need confirmation if this is the right code for my scenario or should it require the row numbers as well?

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

    Please advise.

    Regards, Sara

    Tuesday, October 6, 2020 11:09 PM

Answers

  • That should work, but I'd write it like this:

    Sub Combine_Columns()
        Dim i As Long
        Dim Lastrow As Long
        
        Application.ScreenUpdating = False
        Lastrow = 215
        For i = 2 To 1371
            Cells(1, i).Resize(214).Copy Destination:=Cells(Lastrow, 1)
            Lastrow = Lastrow + 214
        Next i
        
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by SaraPhdNZ Thursday, October 8, 2020 9:01 PM
    Wednesday, October 7, 2020 6:53 AM

All replies

  • That should work, but I'd write it like this:

    Sub Combine_Columns()
        Dim i As Long
        Dim Lastrow As Long
        
        Application.ScreenUpdating = False
        Lastrow = 215
        For i = 2 To 1371
            Cells(1, i).Resize(214).Copy Destination:=Cells(Lastrow, 1)
            Lastrow = Lastrow + 214
        Next i
        
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by SaraPhdNZ Thursday, October 8, 2020 9:01 PM
    Wednesday, October 7, 2020 6:53 AM
  • Thanks heaps Hans, its perfect for my data.

    Regards,

    Sara 

    Thursday, October 8, 2020 9:02 PM