Group and sticky subgroup sorting excel VBA

[jjsauer]

Hello,
I was curious if anybody could point me in the right direction.
Lets say I have 5 categories and each category has 5 subcategories. How could I set up a macro to not only sort the 5 subcategories (descending), but sort the 5 main categories (descending) AND keep the newly sorted subcategory list underneath it?
For example(left is data, right is what I want after the sorting):




Category 1 100 Category 3 500
Subcat 1 1 Subcat 11 5
Subcat 2 2 Subcat 14 4
Subcat 3 3 Subcat 12 3
Subcat 4 4 Subcat 15 2
Subcat 5 5 Subcat 13 1
Category 2 300 Category 5 400
Subcat 6 1 Subcat 21 5
Subcat 7 3 Subcat 24 4
Subcat 8 5 Subcat 23 3
Subcat 9 2 Subcat 22 2
Subcat 10 4 Subcat 25 1
Category 3 500 Category 2 300
Subcat 11 5 Subcat 8 5
Subcat 12 3 Subcat 10 4
Subcat 13 1 Subcat 7 3
Subcat 14 4 Subcat 9 2
Subcat 15 2 Subcat 6 1
Category 4 200 Category 4 200
Subcat 16 1 Subcat 17 5
Subcat 17 5 Subcat 20 4
Subcat 18 3 Subcat 18 3
Subcat 19 2 Subcat 19 2
Subcat 20 4 Subcat 16 1
Category 5 400 Category 1 100
Subcat 21 5 Subcat 5 5
Subcat 22 2 Subcat 4 4
Subcat 23 3 Subcat 3 3
Subcat 24 4 Subcat 2 2
Subcat 25 1 Subcat 1 1


Thanks in advance!

 

[jjsauer]

crap...the spacing didn't stay, I hope you can still understand. Let me know if I should repost it.

 

[jjsauer]

bump

 

[jjsauer]

help!

 

[shg]

Put the major and minor categories in separate columns, duplicating the major category as necessary.

 

[jjsauer]

@ SHG, I don't think that would work. Unless I misunderstood, it basically just sorted everything as though it was in one column. I appreciate it, but it appears I cannot automate everything. I could try a longer, messier macro with multiple sheets, but that just isn't very pretty.