VBA Sorting Columns by Row Values

[RWing]

Hi everyone,

I have the following problem that I hope somebody can help me with.

I have a table, that I want to sort in terms of row values (keeping the existing columns still corresponding with the new row order)

such that for example:

X Y Z
Age 74 53 62
Size 11 5 13

Becomes the following after sorting by age, for example: where Age is defined in a cell which I can then use as a dropdown menu:

Y Z X
Age 53 62 74
Size 5 13 11

I've tried using the following code:, where A1 in the case above, would be Age and the Range defines the table (including the row headings and column headings), but I just get a runtime error 1004. If I do the equivalent thing for sorting column values (change the data range to fit just the column and change to xlSortColumns/xlToptoBottom) it works for columns, but I can't get it to work for rows.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
myKey = Range("A1")
Range("G1:I4").Sort Key1:=myKey, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlSortRows, _
DataOption1:=xlSortNormal
End If
End Sub

Thanks a lot for the help.

 

[RWing]

Anybody? Bump!

Thanks.

 

[Jonmo1]

You have to do it 1 row at a time...

Try

Sub Macro1()
Dim MyRange As Range, i As Long
 
'Adjust range to suit, this should not inlude the column with the text Age and Size
Set MyRange = Range("B2:D3") 
 
For i = 1 To MyRange.Rows.Count

    MyRange(i, 1).Resize(1, MyRange.Columns.Count).Sort Key1:=MyRange(i, 1), _
    Order1:=xlAscending, Orientation:=xlLeftToRight
Next i
End Sub

Hope that helps.

 

[RWing]

You have to do it 1 row at a time...

Try

Sub Macro1()
Dim MyRange As Range, i As Long
 
'Adjust range to suit, this should not inlude the column with the text Age and Size
Set MyRange = Range("B2:D3") 
 
For i = 1 To MyRange.Rows.Count

    MyRange(i, 1).Resize(1, MyRange.Columns.Count).Sort Key1:=MyRange(i, 1), _
    Order1:=xlAscending, Orientation:=xlLeftToRight
Next i
End Sub

Hope that helps.

Hi Jonmo,

Thanks for the help, but I'm not quite sure what you mean. I only want to sort a single row in ascending or descending order of values, but I want the columns attached to the values in each of the rows to move about and be tied to the existing values in the rows which are being sorted.. if that makes sense. *Trying the macro code atm.

 

[Jonmo1]

OK, I see now...

I thought you wanted each row to sort ascending..
but in fact you want only the 1st row to sort ascending, but then the other rows to just follow along with the first.

Try

Sub Macro1()
Dim MyRange As Range
 
'Adjust range to suit, this should not inlude the column with the text Age and Size
Set MyRange = Range("B2:D3")
 
MyRange.Sort Key1:=MyRange(1, 1), _
Order1:=xlAscending, Orientation:=xlLeftToRight
End Sub

 

[RWing]

OK, I see now...

I thought you wanted each row to sort ascending..
but in fact you want only the 1st row to sort ascending, but then the other rows to just follow along with the first.

Try

Sub Macro1()
Dim MyRange As Range
 
'Adjust range to suit, this should not inlude the column with the text Age and Size
Set MyRange = Range("B2:D3")
 
MyRange.Sort Key1:=MyRange(1, 1), _
Order1:=xlAscending, Orientation:=xlLeftToRight
End Sub

Hi, thanks again. I'll try the new code, but your description of what I need isn't right . I have a table... and all the rows are named. I want to be able to type in the Row name in a box somewhere in the sheet, and then ONLY that one row is sorted according to value, and the columns are moved around according to the row values. Edit* The code isn't helping unfortunately!