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.
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!
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments