You are here:

Excel/VBA Sorting Table by certain column excluding last row


QUESTION: Greetings Mr.Ogilvy!

I`ve read about half of your entire answers, before actually getting to write this. More to the point:

Using Excel 2007:

I am trying to devise a sub which will sort out my table based on a single column (actual data stats from cell O4) descending. I also need to exclude the last row, which is a Grand Total row that does not need to be sorted. I need to specify that my entire table has SUBTOTALS in it, and sorting from the graphic interface is really simple (collapse all subtotals, select entire range minus last row, custom sort-descending). But this has proven to be quite the challenge when i try to automatise this. So:

1. table has fixed number of columns (A-X)
2. table has variable number of rows(can range from 500 rows to 3000 from month to month)
3. sort needs to be done by column O - data is in cell O4
4. sort needs to exclude last row
5. sort needs to be descending
6. first 3 rows are frozen

I would be in your debt if you could point me in the right direction, recording macro helped me understand the sorting method, but adjusting it for a dinamic range has proven to be a tad more complicated.

Thanks in advance!!

ANSWER: Steve,

this assumes that  you have data in your worksheet that has had the subtotals applied to it.  This data has 3 rows of headers and the subtotals was applied using the last row of the 3 headers as a single header row.  The subtotals used column O as the sorted column for applying the subtotals and you want to reverse that sort from ascending to descending.  I am making these assumptions based on what you say, particularly about using the graphical interface to do this sort.  I further assume that when the macro is run, the active cell is within your data.  (so the the sheet to be sorted is the active sheet. )

Also there is only one set of subtotals on your data.  The code makes no issue of excluding the grand total line (assuming it is there because of subtotals) because the sort command applied to a subtotalled data set is aware of the grand total line and ignores it.

Sub SortSubtotals()
   Dim r As Range
   Set r = ActiveCell.CurrentRegion
   Set r = r.Offset(2).Resize(r.Rows.Count - 2, 24)
   ActiveSheet.Outline.ShowLevels RowLevels:=2
   ActiveSheet.Sort.SortFields.Add Key:=Range( _
       "O4"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
   With ActiveWorkbook.Worksheets("Sheet1 (2)").Sort
       .SetRange r
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
   End With
   ActiveSheet.Outline.ShowLevels RowLevels:=3
End Sub

So I tested this and it worked for me as I understand your requirement and using the assumptions stated.

Obviously you should test it on a copy of your workbook until you are satisfied it does or does not do what you want.

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Geez, that was fast!!

Will test this and will let you know what happens. If i am not able to tweak/debug IF NECESSARY, I will post a follow up, if it works i will let you know!



If it doesn't work, then let's cut to the chase.  Send me you workbook to

in the state it would be in when you want to run the sort macro and what you want the sort macro to do.


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


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


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.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.