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?

 

[Rick Rothstein]

Give this macro a try...

Sub CopySingleDataRowAndSortIt()
  Dim Data As Range
  Set Data = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
  Data.Copy Data(1).Offset(1)
  With Data.Parent.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=Data.Offset(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Data.Offset(1)
    .Orientation = xlLeftToRight
    .Apply
  End With
End Sub

 

[Technology]

Give this macro a try...

Sub CopySingleDataRowAndSortIt()
  Dim Data As Range
  Set Data = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
  Data.Copy Data(1).Offset(1)
  With Data.Parent.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=Data.Offset(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Data.Offset(1)
    .Orientation = xlLeftToRight
    .Apply
  End With
End Sub

Yes, this works. Thank you! It now requires another separate macro that finds the median of the ordered numbers and place that value in Row 3, Column A. The median should be 4.

 

[Technology]

Rick, are you able to help with finding the median of the order row. I have only been successful when the entries are in a column. Thank you.

 

[aRandomHelper]

finding the median of the order row

Tip: you need to know how many numbers/cells there are (this you know).
if it's odd, divide by 2, round it up. Get the value of the cell in this column's row 2.
if it's even, divide by 2, get the values of the cell in this column's row 2 + the cell in the next column, sum them and divide by 2.

 

[Technology]

Got it! Thank you!

 

[Technology]

I now need to find the occurrence (mode) of the value 10, and place that result in A4.

 

[smozgur]

Yes, this works. Thank you!

@Technology - I can see that @Rick Rothstein's code helped you in this question.
I marked the solution post in this question for you. Next time, when you receive an answer that helped to solve your question, please mark that post as the solution to the question, so future readers might access the solution quickly. Thanks.

 

[Technology]

@smozgur Ok, I see how to do that now. Thank you! I want to be a productive member, and hope that one day I can assist others.

 

[smozgur]

@smozgur Ok, I see how to do that now. Thank you! I want to be a productive member, and hope that one day I can assist others.

You're welcome.
And, welcome to the MrExcel Message Board!