Simple Do... Until problem

[Adam]

Hopefully an easy one for someone

I would like to simplify this macro as is needs to go on for quite a few
more repetitions. I'm hoping your able to spot the pattern. Short tables of
2x7 underneath each other, seperated by a line, that need to be sorted into
order of the highest number in column AV first.

I know it's a Do...Until or a For...Next, but i'm still a little
inexperienced in these areas

Range("AU16:AV22").Select
Range("AV16").Activate
Selection.Sort Key1:=Range("AV16"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("AU24:AV30").Select
Range("AV24").Activate
Selection.Sort Key1:=Range("AV24"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("AU32:AV38").Select
Range("AV32").Activate
Selection.Sort Key1:=Range("AV32"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("AU40:AV46").Select
Range("AV40").Activate
Selection.Sort Key1:=Range("AV40"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Joel]

RowCount = 16
Do while Range("AU" & RowCount) <> ""
Range("AU" & RowCount & ":AV" & (RowCount + 6)).Sort _
Key1:=Range("AV" & RowCount), _
Order1:=xlDescending,
Header:=xlno
RowCount = RowCount + 8
loop

 

[Don Guillett]

Try this idea. To compensate for the skipped line you might try.
Cells(i, mc).OFFSET(2).Resize(6, 2) _

Sub doblocks()
mc = 47 ' Col AU
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row Step 6
Cells(i, mc).Resize(6, 2) _
..Sort Key1:=Cells(i, mc), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next i
End Sub
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Don Guillett]

try this instead

Sub doblocks1()
mc = 47 ' Col AU
For i = 16 To Cells(Rows.Count, mc).End(xlUp).Row Step 8
Cells(i, mc).Resize(6, 2) _
..Sort Key1:=Cells(i, mc), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
MsgBox i
Next i
End Sub

 

[Adam]

Thanks to both - much appreciated

RowCount = 16
Do while Range("AU" & RowCount) <> ""
Range("AU" & RowCount & ":AV" & (RowCount + 6)).Sort _
Key1:=Range("AV" & RowCount), _
Order1:=xlDescending,
Header:=xlno
RowCount = RowCount + 8
loop