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

 

[Louis_Guionneau]

Hi mark,

I spent a bunch of time going through the articles you sent and your code. I understand arrays somewhat but not completely. Do you think you could possibly go through the code you sent and put a brief explanation of what is going on under each line?
Thank you so much! Louis

 

[Louis_Guionneau]

Hi Mark,

I tried to write my own version of the code, as per the below but I can't seem to quite get it right.

Do you think you could work out where it is going wrong?

Thank you

Louis

I don't know if anyone is still looking at this thread... but if they are, I would greatly appreciate any tips on why the below code doesn't seem to be working. It tells me "Compile Error: Invalid Next control variable Reference".

Thank you!

Sub LG_Data_Converter()


Dim Nmbr_Headers As Byte
Nmbr_Headers = Application.InputBox("Input Required", "How many Header Rows are there?", Type:=1, Default:=2)

Dim FirstRow As Long, LastRow As Long, FirstColumn As Long, LastColumn As Long

FirstRow = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

LastRow = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

FirstColumn = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

LastColumn = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Dim No_Data_Rows As Long
No_Data_Rows = LastRow - FirstRow - Nmbr_Headers + 1 '(Inclusive)'

Dim No_Data_Columns As Long
No_Data_Columns = LastColumn - FirstColumn + 1 - 1 '(Take into account the customer ID column)'

Dim Dataset() As Variant
ReDim Dataset(1 To No_Data_Rows, 1 To No_Data_Columns)

Dim i As Long, j As Long

For i = 1 To No_Data_Rows
For j = 1 To No_Data_Columns

Dataset(i, j) = Cells(i, j)

Next i
Next j


End Sub