Excel VBA Code - Sort Subtotals

Question

Hello,

I want to sort a column of numbers that  have been subtotaled in column "Q" based on the data in column "A". The range of rows is dynamic and the range of columns is fixed. How can I write this to accommodate a dynamic range of rows? I tried to change the range from Q2:Q3188 to Q2:Q65000 and A1:R3187 to A1:R65000, however when I run the code nothing changes. Thanks in advance!

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(17), _

        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    ActiveSheet.Outline.ShowLevels RowLevels:=2

Range("Q3").Select

    Range(Selection, Selection.End(xlDown)).Select

    ActiveSheet.Sort.SortFields.Clear

    ActiveSheet.Sort.SortFields.Add2 Key:=Range("Q2:Q3188") _

        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    With ActiveSheet.Sort

        .SetRange Range("A1:R3187")

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

Answer 1

To:  Peruanos72

re:   full speed ahead

Where have you been for the last 3 weeks?  I've had 3 root canals and lost 2 lbs during that time.

The following sort code finds the last row with data in Column R (column 18) and uses that for the bottom right corner of the entire sort range.  Column Q (column 17) is the column sorted.

If data extends below that in column R then the code will need adjusting.

'---

Sub NewSort()

Dim LastRow As Long

Dim rngToSort As Excel.Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, 18).End(xlUp).Row '18 is column R

Set rngToSort = .Range("A1", .Cells(LastRow, 18))

rngToSort.Sort Key1:=.Cells(1, 17), Order1:=xlDescending, _

Header:=xlYes, MatchCase:=False                       '17 is column Q

End With

End Sub

'---

Note: blank cells sort to the  bottom (always)

Don't sort data containing formulas

Excel programs at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Answer 2

It worked! Thank you!

Answer 3

I'd like to say I've been on a remote Caribbean island basking in the sun but alas that is so not the case. 🥺

I was waiting for an email notification saying someone had responded and I never received one. 🤨 So I logged in just to check out my question and saw your response. Now it seems I'm receiving notifications so here I am.

Thx so much for the code. I'll give it a try tomorrow. 

Cheers

Answer 4

To: Peruanos72

re:  sorting subtotals

Sorting a range containing formulas usually doesn't work.

The range references get messed up.

Suggest you try manually moving a couple of rows and see what you get.

(select a row and drag it to another row position)

'---

Excel programs at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

I should have added that the subtotal is not calculated using formulas but rather the "Subtotal" feature on the "Data" tab so the subtotal is a straight number. Does this make a difference? It does sort when I do it manually using that feature. Just need to automate it somehow.  Cheers

Answer 5

To: Peruanos72

re:  sorting subtotals

Sorting a range containing formulas usually doesn't work.

The range references get messed up.

Suggest you try manually moving a couple of rows and see what you get.

(select a row and drag it to another row position)

'---

Excel programs at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents