Turn rows and columns into one row using VBA

[Technology]

How do you take a set of values (rows and columns) in EXCEL and use VBA to puts all those values into one row?

 

[Marc L]

​

Different ways, it depends on the sample you forgot to explain / attach …​

 

[Technology]

7​	3​	8​	6​	3​	6​
1​	6​	9​	4​	2​	5​
5​	10​	10​	4​	6​	2​
1​	7​	6​	7​	0​	6​
1​	5​	6​	3​	9​	7​
1​	7​	8​	8​	1​	6​
2​	8​	0​	6​	0​	2​
7​	5​	10​	1​	3​	0​
8​	5​	9​	9​	4​	0​
6​	4​	3​	2​	4​	2​
8​	4​	7​	6​	6​	9​
6​	4​	1​	4​	3​	1​
2​	3​	0​	8​	8​	4​
0​	3​	5​	4​	7​	6​
7​	1​	0​	0​	8​	3​
4​	1​	8​	3​	9​	6​
4​	1​	1​	10​	3​	5​
7​	3​	0​	4​	1​	4​
3​	6​	6​	9​	6​	1​
8​	2​	3​	6​	6​	8​
0​	0​	1​	10​	1​	2​
5​	2​	9​	4​	3​	7​
5​	7​	3​	8​	1​	6​

 

[Marc L]

​

Now we have a range but without any explanation so that means you are very confident with your Excel / VBA skills to amend any code​

in order to well fit it to your real workbook, are you ?!​

​

Or just well elaborate at least your need with details in order there is nothin' to guess like the range address, the row address, which worksheet(s),​

the expected result, … As guessing can't be coding !​

 

[Technology]

The present values are in the following rows and columns. I am asking for assistance on how, with VBA, do you present the data in one row (let's say row 1) without the use of Excel built-in functions.

 

[Marc L]

​

So under VBA you can use a loop on the column and another one on the rows or vice versa …​

Then with a counter variable you can allocate each value to its destination.​

 

[Rick Rothstein]

Assuming your data starts at cell A1, does this macro do what you want?

Sub MultipleRowsToOneRow()
  Dim R As Long, C As Long, StartRow As Long, LastRow As Long
  Dim Arr As Variant, Result As Variant
  Arr = Range("A1").CurrentRegion
  ReDim Result(1 To UBound(Arr, 1), 1 To UBound(Arr, 1) * UBound(Arr, 2))
  For R = 1 To UBound(Arr, 1)
    For C = 1 To UBound(Arr, 2)
      Result(1, (R - 1) * UBound(Arr, 2) + C) = Arr(R, C)
    Next
  Next
  Range("A1").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End Sub

Please Note
-------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more.

 

[Technology]

Assuming your data starts at cell A1, does this macro do what you want?

Sub MultipleRowsToOneRow()
  Dim R As Long, C As Long, StartRow As Long, LastRow As Long
  Dim Arr As Variant, Result As Variant
  Arr = Range("A1").CurrentRegion
  ReDim Result(1 To UBound(Arr, 1), 1 To UBound(Arr, 1) * UBound(Arr, 2))
  For R = 1 To UBound(Arr, 1)
    For C = 1 To UBound(Arr, 2)
      Result(1, (R - 1) * UBound(Arr, 2) + C) = Arr(R, C)
    Next
  Next
  Range("A1").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End Sub

Please Note
-------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more.

Thank you for the kind words of advice and patience. I don't want to ruffle any feathers, and will do better in providing data. Thank you for your help with the code as well. I took the time to understand and learn from it. Could you also help me with creating one VBA program to order the numbers in the row we you just did and put them in another row (say row 2). Again, I really appreciate it.

 

[Rick Rothstein]

Could you also help me with creating one VBA program to order the numbers in the row we you just did and put them in another row (say row 2). Again, I really appreciate it.

I am not 100% clear on what you want here. Are you asking to add to the macro I posted code with copies the single row that the code just created and then sort that second row into numerical order? If so, what sort order do you want... lowest number on left, highest number on the right or vice versa? Also, should duplicate numbers be kept retained or collapsed down to a single entry?

 

[Technology]

With the posted codes result in that one row, it requires a separate VBA program for this next part. Lowest to highest, left to right. Keep duplicates. Results of ordered values in row 2 of the excel spreadsheet. I hope this helps, I'm trying my best and appreciate the opportunity to learn.