Stack values in Columns / Rows

Louis_Guionneau

New Member
Joined
Nov 12, 2019
Messages
27
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

Well-known Member
Joined
Nov 12, 2010
Messages
11,316
Office Version
365, 2010
Platform
Windows, Mobile
Test the code below on a copy of your worksheet as it deletes data


Code:
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
 
Last edited:

Louis_Guionneau

New Member
Joined
Nov 12, 2019
Messages
27
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
 

Louis_Guionneau

New Member
Joined
Nov 12, 2019
Messages
27
That is pretty awesome. I'm looking through now - it seems to work great on your excel. Amazing! It's so fast!
 

Louis_Guionneau

New Member
Joined
Nov 12, 2019
Messages
27
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

Well-known Member
Joined
Nov 12, 2010
Messages
11,316
Office Version
365, 2010
Platform
Windows, Mobile
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.
 
Last edited:

Louis_Guionneau

New Member
Joined
Nov 12, 2019
Messages
27
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!
 

Forum statistics

Threads
1,077,984
Messages
5,337,529
Members
399,154
Latest member
gavlink

Some videos you may like

This Week's Hot Topics

Top