Sorting by Alpha, Adding Subtotal - then removing subtotal - variable data sets

[gregg_grug]

I am trying to build a macro that will add subtotals, sort then remove the subtotals - on variable data set lengths - I have the attached - but when I add it into a larger macro, it fails;

The part that fails is the sort while the subtotals are on

I think I need to define a region - or something - but really no idea how to! So far just build by recording and adding on

[Kaper]

You start the code with

Selection...

so it depends heavily on what is selected at the beginning.

The easy cure - should be to add at the top of the code:

Worksheets("M-Z").activate 'probably not needed, if it's active all the time
Range("A1:L3000").select 'or other range which you have selected before code starts - may be even just A1

PS. attaching code inside Word doc is a bit unusual - you can put it in the post (just remember to use code tags as required by our https://www.excelforum.com/forum-rul...rum-rules.html ) or inside attached workbook.

[gregg_grug]

Ok so I have found where it keeps breaking. The below code sorts by subtotalled fields for a specified range - however that range is variable. So when I add in more data, then re run, it does not include all the data in the sort.

I tried the Range suggestions above and unfortunately still could not get it working. Thanks in advance for help.

ActiveSheet.Outline.Showlevels RowLevels:=2
ActiveWorkbook.Worksheet("A-L").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("L2:L1094"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("K2:K1094"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("J2:J1094"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("I2:I1094"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("H2:H1094"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("A-L").Sort
        .SetRange Range("A1:N1094")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

[Kaper]

Hi,

I assume that using http://www.excelforum.pl/download.htm?id=69841 I could find what changes / how "range is variable"

So the first suggestion is that data is added or removed at the bottom of the range, column A has some kind of index/unique ID so is filled in every data row.

dim last_row as long
ActiveSheet.Outline.Showlevels RowLevels:=2
last_row = ActiveSheet.cells(rows.count,"A").end(xlup).row
ActiveWorkbook.Worksheet("A-L").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("L2:L" & last_row), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("K2:K" & last_row), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("J2:J" & last_row), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("I2:I" & last_row), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("A-L").Sort.SortFields.Add Key:=Range("H2:H" & last_row), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("A-L").Sort
        .SetRange Range("A1:N" & last_row)
 '...

If that's not enough, then probably my tool from the link above is not strong enough. And then I'll write basically the same as few posts above but in more open way:

Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

[gregg_grug]

Sorry about the delay. I have now replicated the data in original form - and then the desired output.

Couple of things to note;

This macro will be run each month
The number of rows will be different each month (could be less, could be more)
The Macro will start on another tab/sheet
It will need to run on sheet "A-L" & "M-Z"

Thanks again! I'm sure the solution is simple

[Kaper]

Almost a month... At the moment I'm quite busy with other tasks, so anyone who is about to join the discussion - please do.

[gregg_grug]

Bump to see if anyone can assist - thanks

[gregg_grug]

Can anyone help please?