Looping and advancing

mattmickle

Board Regular
Joined
Nov 17, 2010
Messages
81
Hi all,
I know there has to be an easier more efficient way to do this...
In column B I have random numbers, sorted into "books" of 10. I'm looking to sort each of the "books" ascending, but each book by itself.

I recorded some code, but REALLY don't want to have to hardcode and edit each range of cells for the sort (there are 100 books). Each range advances 10, so sort cells B5:B14, then sort cells B15:B24, then B25:B34, etc. etc. etc.

Any help would be appreciated!



Code:
Sub SORT_BOOKS()'
' SORT_BOOKS Macro
'


'
    'BOOK 1
    Range("B5:B14").Select
    ActiveWorkbook.Worksheets("Randomizer").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Randomizer").Sort.SortFields.Add Key:=Range( _
        "B5:B14"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Randomizer").Sort
        .SetRange Range("B5:B14")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    'BOOK 2
    Range("B15:B24").Select
    ActiveWorkbook.Worksheets("Randomizer").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Randomizer").Sort.SortFields.Add Key:=Range( _
        "B15:B24"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Randomizer").Sort
        .SetRange Range("B15:B24")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,717
Office Version
365
Platform
Windows
How about
Code:
Sub SORT_BOOKS() '
' SORT_BOOKS Macro
   Dim i As Long
   Dim Rng As Range
    
   For i = 5 To Range("B" & Rows.count).End(xlUp).Row Step 10
    Set Rng = Range("B" & i).Resize(10)
    ActiveWorkbook.Worksheets("Randomizer").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Randomizer").Sort.SortFields.Add Key:=Rng, _
      SortOn:=xlSortOnValues, order:=xlAscending, DataOption:= _
      xlSortNormal
    With ActiveWorkbook.Worksheets("Randomizer").Sort
        .SetRange Rng
        .header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Next i
End Sub
 

mattmickle

Board Regular
Joined
Nov 17, 2010
Messages
81
Perfect! Thank you so much Fluff! Someday I might even understand how it's doing it's magic!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,717
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,121
Messages
5,466,812
Members
406,499
Latest member
ToyoMike

This Week's Hot Topics

Top