Excel/VBA EXCEL ORDER
QUESTION: Good morning, I have a spreadsheet with columns A through DKL with 61 lines, I sort the columns in pairs A:B C:D E:F to dkl column in descending order always the second column, and wanted to automate classificassão by VBA, a suggestion would be otherwise have to program VBA as below without assemble various constraints for each pair of column. Thank you.
' Macro2 Macro
ActiveWorkbook.Worksheets("Plan1").Sort.SortFields.Add Key:=Range( _
"B2:B61"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
ANSWER: Hello Marcus,
I'm not sure I understand your question. You mention always using the 2nd column as the key. Does this mean the 2nd column of each pair? So B is the key column for A:B, D for C:D, F for E:F, etc.? If this is the case, do you want all the columns left in place on the original sheet? The macro would then sort each column pair independently of all the other columns on the sheet.
---------- FOLLOW-UP ----------
QUESTION: Yes, exactly, the macro will sort each column pair independently and will sort any table to dkl column.
Okay, thank you for the clarification. Here's the code to do all the column pairs out through column DKL:
'Sort columns A:B, C:D, E:F ... DKK:DKL with the second column in each pair serving as
'the sort key.
Dim iCol As Integer
Dim LRow As Long
Dim LCol As Integer 'Last column pair
LRow = 61
LCol = 3002 '(column DKL)
For iCol = 1 To LCol Step 2
.Sort.SortFields.Add Key:=.Range(.Cells(2, iCol + 1), .Cells(LRow, iCol + 1)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.Sort.SetRange .Range(.Cells(1, iCol), .Cells(LRow, iCol + 1))
.Sort.Header = xlYes
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
Note that there is no need to activate B1 (or any other key column cell) and no need to select the column pair. These actions only slow things down by forcing the screen to repeatedly refresh.
I hope you find that this is exactly what you want.