Sorting By Range

Question

I have this and it works, but I need it to sort on the specific range. It may not always be 39 rows down.

   ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("L12:L39") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "AmEx,Discover,Master Card,Visa,Check", DataOption:=xlSortNormal

So I did this, but it still doesn't work.

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("L12", Selection.End(xlDown)) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
    "AmEx,Discover,Master Card,Visa,Check", DataOption:=xlSortNormal

What am I doing wrong? I think this might be simple but I'm just not seeing it.

This is my full code for that part.

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("L12", Selection.End(xlDown)) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
    "AmEx,Discover,Master Card,Visa,Check", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J12", Selection.End(xlDown)) _
    , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("B11", Selection.End(xlDown))
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply

It says 1004 the sort reference is not valid.

'*** Sorting by payment method
    Range("L12").Select                                                     '*** Check this sorting for the correct cells
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("L12", Selection.End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "AmEx,Discover,Master Card,Visa,Check", DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J12", Selection.End(xlDown)) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal


    Dim sht2 As Worksheet
    Dim LastRow2 As Long
    Dim LastColumn2 As Long
    Dim StartCell2 As Range
    Set sht2 = Worksheets("Sheet1")
    Set StartCell2 = Range("B11")
    Worksheets("Sheet1").UsedRange
    LastRow2 = sht2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn2 = StartCell.SpecialCells(xlCellTypeLastCell).Column
    sht2.Range(StartCell2, sht2.Cells(LastRow2, LastColumn2)).Select

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        '.SetRange(StartCell2, sht2.Cells(LastRow2, LastColumn2)).Select
        '.SetRange(StartCell2, sht2.Cells(LastRow2, LastColumn2)).Select
        '.SetRange Range("B11", LastRow2)
        .SetRange Range("B11:AA" & LastRow2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Thanks.

Thanks. I saw that and fixed it but it's still throwing the same error. — Niki, Nov 29 '18 at 20:51
Possible duplicate of [VBA Excel sort range by specific column](https://stackoverflow.com/questions/21451458/vba-excel-sort-range-by-specific-column) — BigBen, Nov 29 '18 at 20:54
I would consider reading [how to avoid using Select in Excel VBA](/q/10714251/5781745). While this may not be specific to your problem, the use of `.Select` is usually unnecessary and in most cases leads to slower code and debugging headaches. Work with your ranges directly. — K.Dᴀᴠɪs, Nov 29 '18 at 21:00
Ben, I changed it to the following and am now getting a new error. Method Range of object_Global failed... .SetRangeRange("B11:ZZ" & LastRow).Sort key1:=Range("AA11:ZZ" & LastRow), _ order1:=xlAscending, Header:=xlNo — Niki, Nov 29 '18 at 21:08
Have you read [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4088852) as suggested above yet? You're still using unqualified references to the ActiveSheet. — Comintern, Nov 29 '18 at 21:11
.SetRange Range("B11", LastRow2) isn't working. I need to grab the last cell. I don't think LastRow2 corresponds to AA39. — Niki, Nov 29 '18 at 21:22
I read the article and it makes sense, but it's not showing you how to get an unspecified range. It just shows you how to hard code a specified range. Can anyone help? — Niki, Nov 29 '18 at 21:51
I managed to get rid of the "sort reference not valid" message by adding the line "ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear" above your With block. Not sure if this will help you, though. — Brad, Nov 29 '18 at 21:58
Thanks, Brad. That gets rid of the error but then it doesn't sort it properly. The problem is the following code that I got of an instructional Excel webiste is wrong. This doesn't grab the last row. "LastRow2 = StartCell.SpecialCells(xlCellTypeLastCell).Row" So... .SetRange Range("B11:AA" & LastRow2) will never work unless there's a value in every cell, which is not my data. — Niki, Nov 29 '18 at 22:03
LastRow2 = sht2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Ok this works. It grabs the data, but I'm still getting an error. It says The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank. It takes me to .Apply — Niki, Nov 29 '18 at 22:08

K.Dᴀᴠɪs · Accepted Answer · 2018-11-29T22:14:21.250

You should take a look at these functions. I use them quite often, and they are saved in my Personal Workbook for quick and easy referencing.

Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
    With ws
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
End Function

Function LastColumn(ws As Worksheet, Optional rowNum As Long = 1)
    With ws
        LastColumn = .Cells(rowNum, .Columns.Count).End(xlToLeft).Column
    End With
End Function

In conjunction with the above functions, you could use this

Dim ws As Worksheet
Dim sortRng As Range, rngL As Range, rngJ As Range
Dim lstRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lstRow = lastRow(ws, "J")

With ws

    Set sortRng = .Range("B11", .Cells(lstRow, LastColumn(ws, 11)))
    Set rngJ = .Range("J11:J" & lstRow)
    Set rngL = .Range("L11:L" & lstRow)

    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=rngJ, SortOn:=xlSortOnValues, Order:=xlAscending, _
                CustomOrder:="AmEx,Discover,Master Card,Visa,Check", DataOption:=xlSortNormal
        .SortFields.Add Key:=rngL, SortOn:=xlSortOnValues, Order:=xlDescending, _
                DataOption:=xlSortNormal
        .SetRange sortRng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End With

Take note: No use of .Select anywhere in the above code. Start with this and learn to avoid using select, as it's rarely necessary.

Breaking down the code

You will set your entire sort area using this line:

Set sortRng = .Range("B11", .Cells(lstRow, LastColumn(ws, 11)))

These lines are the ranges of the specific columns you are wanting to sort.

Set rngJ = .Range("J11:J" & lstRow)
Set rngL = .Range("L11:L" & lstRow)

Make sure that you clear any prior criteria

.SortFields.Clear

Then add your criteria (your custom sort, for example)

.SortFields.Add Key:=rngJ, SortOn:=xlSortOnValues, Order:=xlAscending, _
    CustomOrder:="AmEx,Discover,Master Card,Visa,Check", DataOption:=xlSortNormal
.SortFields.Add Key:=rngL, SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal

Thank you! I'm not an Excel programmer (obviously) so I kind of needed a crash course, as it always is. I'll take a look and get back to you, and hopefully mark as answered. Thanks for your help. I really appreciate it. I've been ticking around on this for two days, but at least I learned a lot. — Niki, Nov 29 '18 at 22:16
Thanks. I got it to work, but there's something wrong with the sort. It doesn't error out, but it doesn't sort the columns correctly. I'm not sure what it's doing. I just have your code. I even commented out the custom sort and have a blank worksheet with a few numbers in it and it doesn't sort them in ascending or descending order. Any suggestions? — Niki, Dec 11 '18 at 19:58
Well, remember that you have a custom sort: `AmEx,Discover,Master Card,Check` and that it won't sort numbers. This will have to be in column J, and then you have other criteria in column L that sorts alphabetically (descending). — K.Dᴀᴠɪs, Dec 11 '18 at 20:47

Sorting By Range

1 Answers1

Breaking down the code