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
– Cyril, Commented Jun 20, 2019 at 20:50

Cyril · Accepted Answer · 2019-06-20 20:54:50Z

1

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.

answered Jun 20, 2019 at 20:54

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting Data in Multiple Columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related