ExcelBanter - Simple Do... Until problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Simple Do... Until problem (https://www.excelbanter.com/excel-programming/433358-simple-do-until-problem.html)

Adam

Simple Do... Until problem

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

Simple Do... Until problem

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

"Adam" wrote:

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

Don Guillett

Simple Do... Until problem

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
Microsoft MVP Excel
SalesAid Software

"Adam" wrote in message
...
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

Don Guillett

Simple Do... Until problem

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Adam" wrote in message
...
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

Adam

Simple Do... Until problem

Thanks to both - much appreciated

"Joel" wrote:

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

"Adam" wrote:

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

All times are GMT +1. The time now is 10:23 PM.