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!!
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.
Dim r As Range
Set r = ActiveCell.CurrentRegion
Set r = r.Offset(2).Resize(r.Rows.Count - 2, 24)
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"O4"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
With ActiveWorkbook.Worksheets("Sheet1 (2)").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
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.
---------- 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 firstname.lastname@example.org
in the state it would be in when you want to run the sort macro and what you want the sort macro to do.