Sorting Data in Multiple Columns

Question

I'm trying to sort data in multiple columns(9, to be exact). Essentially I need to sort them by an individual # (The sort number), while keeping the rest of the information in the remaining columns aligned with the sort numbers. I tried recording this as a macro and extracting the code, but when I tried to recreate that code using relative cell selection, I was unsuccessful. I attached a screenshot of the excel doc I'm working with.

Screenshot

Range("B6").Select

Range(Cells(Selection.Column, 2), Cells((Selection.Column), 26)).Select
ActiveWorkbook.Worksheets("Liability.VarAnn & Other").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Liability.VarAnn & Other").Sort.SortFields.Add Key _
    :=Range(Cells(Selection.Column, 2), Cells((Selection.Column), 26)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Liability.VarAnn & Other").Sort
    .SetRange Range(Cells(Selection.Column, 2), Cells((Selection.Column), 26))
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

If anyone can help, thank you in advance!

So you just want a general sort of the data, based on the Group Number (key)? — Cyril, Jun 20 '19 at 20:50
Can you please[edit your question](https://stackoverflow.com/posts/56693614/edit) to include your code? — cybernetic.nomad, Jun 20 '19 at 20:50

score 1 · Accepted Answer · answered Jun 20 '19 at 20:54

Sort works by identifying the region to be sorted, then sorting upon a key in a particular column:

.Range(.Cells(1,1),.Cells(lr,lc)).Sort key1:=Range(.Cells(1,"H"),.Cells(lr,"H")), order1:=xlAscending, Header:=xlNo

So if you need to limit one of those two ranges, you can without affecting data not in the region to be sorted (from above, .Range(.Cells(1,1),.Cells(lr,lc))).

If i only want to sort Column H of a range, rather than A1 through the last column (lc) and last row (lr), I could by:

.Range(.Cells(1,"H"),.Cells(lr,"H")).Sort key1:=Range(.Cells(1,"H"),.Cells(lr,"H")), order1:=xlAscending, Header:=xlNo

Also, please see How to avoid using Select in Excel VBA.

Sorting Data in Multiple Columns

1 Answers1