Loop or repeat a recorded sort macro

[gregggrown]

Hi all, I have a workbook where I need to sort data with range (K2:L24), then move down 208 rows and sort range (K210:L232) and repeat this upto 1000 times. I have shortened a recorded macro as an example and would like to know how to loop or repeat please.

Thanks


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/04/2011 by Greg
'
' Keyboard Shortcut: Ctrl+x
'
Range("K1:L24").Select
Selection.sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("K209:L232").Select
Selection.sort Key1:=Range("L210"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("K417:L440").Select
Selection.sort Key1:=Range("L418"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("K625:L648").Select
Selection.sort Key1:=Range("L626"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

 

[Warship]

Try: (not tested)

Sub Macro2()
    Dim K As Long, L As Long
    K = 1: L = 2
    Do While Range("K" & K) <> ""
        Range("K" & K & ":L" & K + 23).Sort Key1:=Range("L" & L), _
        Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        K = K + 208
        L = L + 208
    Loop
End Sub

 

[gregggrown]

Sorry for the delayed response Warship, works great.
Thanks for your help

 

[Warship]

Thanks for the feedback - I'm glad it worked.

 

[yash203]

Hi all,
Help required, Need to repeat/loop this recorded macro..
Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Selection.Copy
Sheets("OUT").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("PLANT").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("OUT").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Copy
ActiveCell.Range("A1:A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(10, 0).Range("A1").Select
Sheets("PLANT").Select
ActiveWindow.LargeScroll ToRight:=-3
ActiveCell.Select
Sheets("MAT").Select
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub