Stack values in Columns / Rows

[Louis_Guionneau]

Hi Everyone,

Thanks for this awesome forum. I've spent a lot of time on here in the past and am normally able to get answers from previous posts but this time I can't so trying to post myself.

I want to write a macro that takes the values of a set of columns (the number of columns will vary depending on the dataset) and paste all the values of the individual rows in column A. For example, if, in a given dataset, column A has 50 rows of data; column B has 30 rows of data; column C has 60 rows of data, then i would want the macro to update column A with values from source A1 - 50; then B1 - B30; then C1 -60 (and also delete the data in columns B and C). So column A would now have 140 rows of data, with the values that were previously in Column A listed first, then values in Column B, then values in Column C. I hope this is somewhat clear.


I've been struggling for a while to write something that works and will dynamically and automatically loop through all the columns and rows in a given dataset. I've started by coming up with the below but it doesn't work (I'm still a beginner at VBA, I know there are issues with the below, warning you in advance ).

I'd really appreciate any guidance you all can offer.

Thank you

_____________


Sub StackColumns()


Dim LastColumn As Long
LastColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column


Dim i As Long


For i = 2 To LastColumn


Dim CopyDestinationRow As Long
CopyDestinationRow = Cells(1, 1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


Dim LastRow As Long


LastRow = Cells(1, i).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range(Cells(1, i), Cells(LastRow, i)).Copy Cells(CopyDestination, 1)


Next


End Sub

 

[MARK858]

Test the code below on a copy of your worksheet as it deletes data

Sub OneColumn()

    Dim vCll, vOutP() As Variant
    Dim i As Long, j As Long, lRow As Long

    vCll = ActiveSheet.UsedRange.Value

    ReDim vOutP(1 To UBound(vCll, 1) * UBound(vCll, 2), 1 To 1)

    For j = LBound(vCll, 2) To UBound(vCll, 2)
        For i = LBound(vCll, 1) To UBound(vCll, 1)
            If Len(vCll(i, j)) > 0 Then
                lRow = lRow + 1
                vOutP(lRow, 1) = vCll(i, j)
            End If
        Next i
    Next j

    ActiveSheet.UsedRange.EntireColumn.Delete
    Cells(1, 1).Resize(lRow).Value = vOutP


End Sub

 

[Louis_Guionneau]

Hi Mark,

Thanks so much for your response! I tried this code on a dataset and it doesn't seem to be having the desired effect (code doesn't seem to have any impact on my data). I expect I'm doing something wrong and thank you again for your help.

Louis

 

[MARK858]

Seems to work fine for me, try clicking the button on the file in the link (any sheet) and if it appears to do what you want tell me what the difference is with your workbook.

https://app.box.com/s/0ycgt3poiyc1kuffp461m0rg3xdtspzi

 

[Louis_Guionneau]

That is pretty awesome. I'm looking through now - it seems to work great on your excel. Amazing! It's so fast!

 

[Louis_Guionneau]

OK, I retract my earlier post - it does indeed work. I was being dumb and was trying to follow it step by step by hitting F8 and didn't get to the meat of the code, so thought it didn't work. I clicked "Run" and it works GREAT. You're AMAZING!! Thank you.

 

[MARK858]

You're welcome.

I was being dumb and was trying to follow it step by step by hitting F8

That is not being dumb, it is the usual way you learn what is happening but the code as written only interacts with the worksheet on the last 2 lines (it does the rest in memory) so you don't see much using F8. To see what is happening then either set up a Watch or open up the Locals Window and watch that as you step through.

 

[Louis_Guionneau]

I appreciate that! Wow, thank you again. I can't get over how fast it runs. I want to be able to understand what's going on so that I can potentially tweak it down the line. This is so cool!

 

[MARK858]

If you want to understand how it works and you haven't had any dealings with arrays then it might be worth you having a look at the links below (not sure which one will be the easiest for you, to a certain extent they cover the same areas).

http://www.snb-vba.eu/VBA_Arrays_en.html
https://excelmacromastery.com/excel-vba-array

 

[Louis_Guionneau]

OK, thank you! I'll definitely look through these.