Sorting Subtotals

Resolved · Low Priority · Version 2003

Mon 24 Aug 2009, 12:55 Delegate Nick said...

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?

Mon 24 Aug 2009, 13:31 Trainer Amanda said...

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

Mon 24 Aug 2009, 14:01 Delegate Nick said...

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

Excel tip:

Multiple Lines of Text in a Cell

As an alternative to the Text Wrapping facility, type a word or two, press Alt+Enter to get a new line, type more text, and continue the process for as many lines as you need. Enter as normal when you have finished.

The line break is not affected by changing the column width, as text wrapping. To remove this you must edit the cell and remove the invisible character and replace with a normal space.

View all Excel hints and tips

Sorting Subtotals

Sorting Subtotals

RE: Sorting Subtotals

RE: Sorting Subtotals

Excel articles

Excel tip:

Multiple Lines of Text in a Cell