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 (making the existing columns corresponding with the new row order). Essentially, I want
to be able to select a row name from a dropdown list and have it automatically sort the columns by the values in that row.
Doing the dropdown list is straight forward enough, and I can make the code for sorting rows based on column values, but not
the other way around , as shown below:
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 dropdown list
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.
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,
Also I've tried using the macro recorder to figure out a route, but that selects the row manually and I'm finding it
difficult to integrate the search code into that code so that it sorts only a named row.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("$B$3"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
Thanks a lot for the help!