Nick has attended:
Excel VBA Intro Intermediate course
Sorting Subtotals
Is there a way of ensuring Subtotals stay group when using an a-z sort?
RE: Sorting Subtotals
Hello Nick
Thank you for your question and welcome to the forum.
Are these subtotals you have added to the spreadsheet using Data - Subtotal or by another means?
Kind regards
Amanda
RE: Sorting Subtotals
Hi Amanda,
The subtotals are added by a visual basic macro as below!
Many Thanks,
Nick
' Sort by name and subtotal
Columns("A:AF").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8, 9, _
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, _
28, 29, 30, 31, 32), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Font.Bold = True
