Slight Tweak To Recorded Macro

[Dazzawm]

I have recorded a sort macro. What I need to do is change where it says range C5880:C5898, AK5880:AK5898 etc. Can it be selected range of rows I choose as they will be different each time I use? Thanks

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 key:=Range( _
        "C5880:C5898"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 key:=Range( _
        "AK5880:AK5898"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 key:=Range( _
        "AF5880:AF5898"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A5880:AY5898")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Legacy 143009]

This may be an option:

Sub Macro1()
  Dim sortRange As Range
  Set sortRange= Application.InputBox("Enter the range to search in:", Type:=8)

  With ActiveWorkbook.Worksheets("Sheet1").Sort
  .SortFields.Clear
  .SortFields.Add2 key:=sortRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .SetRange sortRange
  .Header = xlGuess
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
End Sub

 

[Joe4]

Here is a little example that shows how you can get the first and last rows of your selected range, and then build a new range out of it, depending on what columns you want to apply it to:

Sub BuildRange()

    Dim fr As Long
    Dim lr As Long
    Dim rng As Range
    
    With Selection
        'Get first row in selection
        fr = .Cells(1, 1).Row
        'Get last row in selection
        lr = .Rows(.Rows.Count).Row
    End With
    
    'Build dynamic range
    Set rng = Range("C" & fr & ":C" & lr)
    
    MsgBox rng.Address
    
End Sub

 

[Dazzawm]

Thanks both @Joe4 and @Flashbond but I don't really know what to do with either of them.

 

[Joe4]

Thanks both @Joe4 and @Flashbond but I don't really know what to do with either of them.

You said you want to replace ranges like Range("C5880:C5898") with a dynamic range that is based on the rows you have selected prior to running your code, right?
So you could put the body of my code at the top of your code (minus the MsgBox part, you do not need that, that was just to show you the range you built), and then replace the Range("C5880:C5898") reference in your code with rng.

You could build 3 other ranges using the same variables for first and last rows.
So your code might look like this:

Sub Macro1()
'
' Macro1 Macro
'
    Dim fr As Long
    Dim lr As Long
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
    
    With Selection
        'Get first row in selection
        fr = .Cells(1, 1).Row
        'Get last row in selection
        lr = .Rows(.Rows.Count).Row
    End With
    
    'Build dynamic ranges
    Set rng1 = Range("C" & fr & ":C" & lr)
    Set rng2 = Range("AK" & fr & ":AK" & lr)
    Set rng3 = Range("AF" & fr & ":AF" & lr)
    Set rng4 = Range("A" & fr & ":AY" & lr)
'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=rng1, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=rng2, _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=rng3, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rng4
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

 

[Legacy 143009]

In my example, it will promt you to select a range each time you run the macro.

 

[Dazzawm]

You said you want to replace ranges like Range("C5880:C5898") with a dynamic range that is based on the rows you have selected prior to running your code, right?
So you could put the body of my code at the top of your code (minus the MsgBox part, you do not need that, that was just to show you the range you built), and then replace the Range("C5880:C5898") reference in your code with rng.

You could build 3 other ranges using the same variables for first and last rows.
So your code might look like this:

Sub Macro1()
'
' Macro1 Macro
'
    Dim fr As Long
    Dim lr As Long
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
   
    With Selection
        'Get first row in selection
        fr = .Cells(1, 1).Row
        'Get last row in selection
        lr = .Rows(.Rows.Count).Row
    End With
   
    'Build dynamic ranges
    Set rng1 = Range("C" & fr & ":C" & lr)
    Set rng2 = Range("AK" & fr & ":AK" & lr)
    Set rng3 = Range("AF" & fr & ":AF" & lr)
    Set rng4 = Range("A" & fr & ":AY" & lr)
'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=rng1, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=rng2, _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=rng3, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rng4
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
End Sub

Much obliged that works for me.

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[Dazzawm]

In my example, it will promt you to select a range each time you run the macro.

I ran it and selected rows 400 - 500 for example but it didn't ask me what columns to sort by and in what order etc?

 

[Dazzawm]

You are welcome.
Glad we were able to help.

Not sure where the column A sort comes into it though as I didn't choose that in my recorded macro?