Stack up multiple columns (variable size) into one column with a function.

[pedexe90]

Hello everyone,

This might be a tricky one but I'm sure someone will somehow magically solve it.

So I have 5 columns lets say occupying cells in B:B, C:C, D:D, E:E, F:F with variable sizes. I want column G:G to be populated or in other words to stack up all values from these 5 columns. So the function should know when each column ends and to move to the other. I don't mind to manually put these columns in the function as long as it would know when it ends and to start stacking up values from the start of the next column.

Many thanks,

 

[My Aswer Is This]

Try this:

Sub Test()
'Modified 5/13/2018 4:15 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowg As Long
    For i = 2 To 6
        Lastrow = Cells(Rows.Count, i).End(xlUp).Row
        Lastrowg = Cells(Rows.Count, 7).End(xlUp).Row + 1
        Cells(1, i).Resize(Lastrow).Copy Cells(Lastrowg, "G")
    Next
Application.ScreenUpdating = True
End Sub

 

[pedexe90]

Try this:

Sub Test()
'Modified 5/13/2018 4:15 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowg As Long
    For i = 2 To 6
        Lastrow = Cells(Rows.Count, i).End(xlUp).Row
        Lastrowg = Cells(Rows.Count, 7).End(xlUp).Row + 1
        Cells(1, i).Resize(Lastrow).Copy Cells(Lastrowg, "G")
    Next
Application.ScreenUpdating = True
End Sub

Thank you for you quick reply.

Is there a function that would do this without using Macro. The following function does it but I want it to skip the first (title) cell on each column and only stack up values.

=IF(ROW()<=COUNTA(A:A),INDEX(A:A,ROW()),IF(ROW()<=COUNTA(A:B),INDEX(B:B,ROW()-COUNTA(A:A)),IF(ROW()>COUNTA(A:C),"",INDEX(C:C,ROW()-COUNTA(A:B)))))

Thanks

 

[My Aswer Is This]

To all of my knowledge a Function is using Vba or as some call it a Macro. A function is just run differently.
I'm not a expert on functions.
Maybe someone else here at Mr. Excel will be able to help you.

A Macro is actually Vba that has been written by a recording devise in Excel. Sort of like a tape recorder recording your every action.