Auto sorting multiple ranges on event

[rlutes]

I have a Worksheet to sort, that has similar data to the table below:

A B C
1 Category Number Level
2 Black 05 A
3 Black 14 A
4 Black 14 C
5 Black 16 B
6 Total 49
7 White 03 B
8 White 03 C
9 Total 6
10 Red 04 A
11 Red 04 B
12 Red 04 C
13 Red 16 B
14 Total 28


There are three sort areas in this example: Black, White & Red. I want
to sort each area using columns B as Key1 and C as Key2. Column A would
be the same value with no need to sort. The example is already sorted
correctly.

If someone just pasted this into a worksheet, but with each area out of
sequence, I’m looking for a vba script which would recognize each area,
determine the number of rows, set this as the sort area and using B & C
as Keys, sort alphabetically the area.

 

[Nigel]

Hi, Try this sort the data, it assumes that after each set there is a Total
row!

Sub Sorter()
Dim lrow As Long, ir As Long, key As String, r1 As Long, r2 As Long

lrow = Cells(Rows.Count, 1).End(xlUp).Row
ir = 2
While ir <= lrow
key = Cells(ir, 1).Value
r1 = ir 'set start of range to sort
While key = Cells(ir, 1).Value
ir = ir + 1
Wend
r2 = ir - 1 'set end of range to sort
ir = ir + 1 ' skip the totals row
Range(Cells(r1, 1), Cells(r2, 3)).sort Key1:=Cells(2, 2),
Order1:=xlAscending, Key2:=Cells(2, 3) _
, Order2:=xlAscending, Header:=xlNo
Wend
End Sub

 

[rlutes]

Thanks Nigel, I'll give it a try. Just got back in town