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.

Sub Macro2()
' Macro2 Macro
   ActiveWorkbook.Worksheets("Plan1").Sort.SortFields.Add Key:=Range( _
       "B2:B61"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
   With ActiveWorkbook.Worksheets("Plan1").Sort
       .SetRange Range("A1:B61")
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
   End With
End Sub

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.

Hi Marcus,

Okay, thank you for the clarification.  Here's the code to do all the column pairs out through column DKL:

Sub SortColumnPairs()
  '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)
  With Worksheets("Plan1")
     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
     Next iCol
  End With
End Sub

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.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Damon Ostrander


I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.


I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2017 About.com. All rights reserved.