Looping and advancing

[mattmickle]

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!

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

 

[Fluff]

How about

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]

Perfect! Thank you so much Fluff! Someday I might even understand how it's doing it's magic!

 

[Fluff]

Glad to help & thanks for the feedback