Recording an Excel Macro to Sort with Relative References

Hankster

Not sure why you need a macro to sort differing ranges on different sheets (just the normal sort would probably do that) but I think I'd drop relative recording as the answer.

You code needs to be in a module (as the Record a Macro function delivers). Try these changes (in bold) where you're asked to drag out the range to sort (e.g. A1:B35) and then a single key to sort on (e.g. B1):

Sub SortTry()
'
' SortTry Macro
'
Dim jRng As Range, jKey As Range 'variables for range and sort key

'get the range to sort, Type 8 returns a range
Set jRng = Application.InputBox(Title:="Sort this sheet...", Prompt:="Select the range to sort", Type:=8)
Range("A1").Activate ' pick top row then get key
Set jKey = Application.InputBox(Title:="... with this key", Prompt:="Enter the header to sort data by", Type:=8)

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=jKey, SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange jRng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Should work on any active sheet.

Hope this helps.

When constructing a sort it's important to get the syntax levels right. Here they are. The Sort is within the sheet and the SortFields are within the Sort and each level is indented by one level, begining to end.

  With ActiveWorkbook.Worksheets("Sheet1")        ' avoid using "ActiveSheet"

         With .Sort

            With .SortFields

            End With

        End With

    End With

ActiveSheet is the tab you last looked at. You will stop using this after you ran the code on   a wrong sheet by mistake a few times but you are welcome to heed my advice: refer to the sheet by name in your code.

The next thing to avoid is Offset. If you know that you mean A2, don't refer to it as Range("A1").Offset(1). In fact, refer to a cell using cell addressing syntax, a range using range addressing syntax. Cells are addressed by Row & Column coordinates, ranges by their names (you can concatenate a range name from cell references). Avoid using ActiveCell.

With that said, you define the range to sort. In your case that is A1:B35. That includes headers but that's not a problem that you overcome with Offset. Instead you have the Sort property "Headers" which you plan to set as "Yes". It says that the SetRange includes one row of headers.

Now you are reader to set up the SortFields. Start by removing any existing ones. Then understand that a "SortField" is the first cell of a column, not necessarily part of the range to sort. You need to tell Excel to "Sort on column A". "A" is the first column of both your tab and your sort range. Therefore the Sortfield := 1 (or any expression that evaluates to "1"). Bear in mind that sorting will be done on the SortField. Any other columns included in the RangeToSort will remain unsorted, but rows will stay aligned. You can set a second and third Sortfield to first sort on column A, then collumn B etc.

And that's the entire setup. You can now easily adapt the code below to any range and any sheet.

Private Sub SortMyRange()

    Dim RngToSort       As Range

    With ActiveWorkbook.Worksheets("Sheet1")        ' avoid using "ActiveSheet"
        Set RngToSort = .Range("A1:B35")
        Set SortFieldsRng = .Range("A1:B1")
        With .Sort
            With .SortFields
                .Clear
                .Add2 Key:=RngToSort.Columns(1), _
                      SortOn:=xlSortOnValues, _
                      Order:=xlDescending, _
                      DataOption:=xlSortNormal
            End With

            .SetRange RngToSort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Thank you for sharing this good post, I am very impressed with your post, the information given is meticulous and easy to understand. I will follow your next post regularly.  https://shell-shockers.online