Stack values in Columns / Rows

Louis_Guionneau

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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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:
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top