You are here:

Excel/VBA solution to sort multiple cells in multiple rows

Advertisement


Question
Sortrows
Sortrows  
Hi Tom,

RE: Excel 2007, VBA solution to sort multiple cells in multiple rows in ascending order.

I am using Excel 2007 for Windows.  My worksheet consists of multiple columns and dynamically expanding rows.  The data I would like to sort is all numeric: I have a mixture of constants and formula based values. The data may contain duplicate values.  I would appreciate if the VBA code could be as flexible as possible using variables to select which cells within each row to sort (not the entire row).
I would like a VBA solution to sort multiple cells in a row, in ascending order.  Each row should be sorted separately, and independently of each other.

Row 13 is a header with various labels.
Row 14 is start of Data.
Columns A AJ contain Data.
I need to sort different ranges; using variables to change the range would be helpful.  As an example, the cells to sort are within columns H to Z (variables to change the range).  Each row of Data to be sorted separately in ascending order.

Sample  - Data unsorted:
Column   H   I   J   K   L   M   N   O   P   Q   R   S   T   U   V   W   X   Y   Z
Row 14   0   99   76   121   4   90   54   123   100   118   64   44   34   88   79   99   111   71   99
Row 15   84   23   90   121   111   133   113   9   78   10   87   67   57   111   102   122   134   94   122
Row 16   1   0   0   140   130   152   132   28   97   29   106   86   76   130   121   141   153   113   141

Sample  - Expected Sorted Data:
Column   H   I   J   K   L   M   N   O   P   Q   R   S   T   U   V   W   X   Y   Z
Row 14   0   4   34   44   54   64   71   76   79   88   90   99   99   99   100   111   118   121   123
Row 15   9   10   23   57   67   78   84   87   90   94   102   111   111   113   121   122   122   133   134
Row 16   0   0   1   28   29   76   86   97   106   113   121   130   130   132   140   141   141   152   153

Hope you can help.
Thanks,
Sam

Answer
Sam,


Select the rectangular area of cells you want to sort and run the macro:
(do this on a copy of your workbook)

Sub SortSelection()
Dim rw As Range
With ActiveSheet.Sort
   For Each rw In Selection.Rows

    .SortFields.Clear
    .SortFields.Add _
       Key:=rw, _
       SortOn:=xlSortOnValues, _
       Order:=xlAscending, _
       DataOption:=xlSortNormal
    .SetRange rw
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlLeftToRight
    .SortMethod = xlPinYin
    .Apply
    Next
 End With
End Sub

--
Regards,
Tom Ogilvy

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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.