| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
| simple for each problem | Excel Programming | |||
| Simple for.. next problem | Excel Programming | |||
| Need help with a simple problem | Excel Programming | |||
| A simple problem... | Excel Programming | |||
Hybrid Mode
