Subscript Out of Range When Sorting

[reberryjr]

I don't know why I'm having issues with sorting, as I've done it a number of times in other projects. I've referred to my old code, as well as the web and I've tried the following snippets to no avail. I keep getting a "subscript out of range" error. Even when I copy and paste the macro recorder's snippet, I get an error.

'***Attempt 1
'ActiveWorkbook.Worksheets("DOADS").Sort Key:=Range("AH1:AH" & mPNLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
'With ActiveWorkbook.Worksheets("DOADS").Sort
'    .SetRange Range("AH2:AH" & mPNLR)
'    .Header = xlNo
'    .MatchCase = False
'    .Orientation = xlTopToBottom
'    .SortMethod = xlPinYin
'    .Apply
'End With

'Attempt 2
'mP.Range("AH1:AH" & mPNLR).Sort Key1:=Range("AH1"), order1:=xlAscending, Header:=xlYes

'Attempt 3
'mP.Sort.SortFields.Clear

'mP.Sort.SortFields.Add Key:=mP.Range("AH1:AH" & mPNLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal, Header:=xlYes

'Attempt 4
'mP.Range("AH1:AH" & mPNLR).Sort Key:=mP.Range("AH1:AH" & mPNLR), Order:=xlAscending, Header:=xlYes

'Attempt 5
'ActiveWorkbook.Worksheets("Names").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Add Key:=Range("AF1:AF" & mLiqLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

'With ActiveWorkbook.Worksheets("Names").Sort
'    .SetRange Range("AF2:AF" & mLiqLR)
'    .Header = xlNo
'    .MatchCase = False
'    .Orientation = xlTopToBottom
'    .SortMethod = xlPinYin
'    .Apply
'End With

 

[reberryjr]

It seems to work just fine for me.

Do you have any merged or protected cells in the workbook
Or perhaps any errors in your data?

There are merged cells in the workbook, but not on this worksheet. Not sure what errors could be in the data...it's literally just 14 names and no protected cells

 

[Joe4]

I cannot reproduce your error.
Without access to your workbook, I don't thing there is anything else I can do.

Are you able to strip your workbook down to this particular code and data, and share it with us?

 

[reberryjr]

I cannot reproduce your error.
Without access to your workbook, I don't thing there is anything else I can do.

Are you able to strip your workbook down to this particular code and data, and share it with us?

My work laptop won't allow me to do that. I did figure out part of the issue...the activeworkbook portion of the snippet was referring to the incorrect workbook. I still can't get the darn column to sort though. I'll keep playing with it. Maybe if I throw the sorting piece in a new module, with no other code...

 

[Joe4]

My work laptop won't allow me to do that. I did figure out part of the issue...the activeworkbook portion of the snippet was referring to the incorrect workbook. I still can't get the darn column to sort though. I'll keep playing with it. Maybe if I throw the sorting piece in a new module, with no other code...

Is it in a General Module now, or a Sheet or Workbook module?
Best place to have it is a General Module.

I find that in trying to debug these things, it is often helpful to step into the code and proceed line-by-line, using the F8 key, while watching what is going on on your sheet.
Often, the issue becomes evident then (especially if you not in the right workbook or worksheet).

 

[reberryjr]

Is it in a General Module now, or a Sheet or Workbook module?
Best place to have it is a General Module.

I find that in trying to debug these things, it is often helpful to step into the code and proceed line-by-line, using the F8 key, while watching what is going on on your sheet.
Often, the issue becomes evident then (especially if you not in the right workbook or worksheet).

It's in a General module. So, this snippet worked in a new module, and when I copied it over to the desired module. However, when I try to extrapolate this code to a different column (there are 5 standalone columns I want to do this to, as they feed dropdown lists), the sorting in column AL is "cleared" per the code. If I comment that line out, I get an error at the .Apply line, saying the sort reference is not valid.

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Add2 Key:=Range("AL1:AL" & mPNLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Names").Sort
    .SetRange Range("AL1:AL" & mPNLR)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

 

[Joe4]

It's in a General module. So, this snippet worked in a new module, and when I copied it over to the desired module. However, when I try to extrapolate this code to a different column (there are 5 standalone columns I want to do this to, as they feed dropdown lists), the sorting in column AL is "cleared" per the code. If I comment that line out, I get an error at the .Apply line, saying the sort reference is not valid.

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Add2 Key:=Range("AL1:AL" & mPNLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Names").Sort
    .SetRange Range("AL1:AL" & mPNLR)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

I would check/confirm value of "mPNLR" there, like we did with the other variable.
And then make sure that there are no errors, merged cells, or protected cells on that sheet.

 

[reberryjr]

I would check/confirm value of "mPNLR" there, like we did with the other variable.
And then make sure that there are no errors, merged cells, or protected cells on that sheet.

I have no idea why it started working, but this is the final product:

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Add2 Key:=Range("AL1:AL" & mPNLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Names").Sort
    .SetRange Range("AL1:AL" & mPNLR)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

 

[Joe4]

I have no idea why it started working, but this is the final product:

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Names").Sort.SortFields.Add2 Key:=Range("AL1:AL" & mPNLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Names").Sort
    .SetRange Range("AL1:AL" & mPNLR)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

If you made no changes, and it is working now where it wasn't before, it may have something to do with which workbook you are in and/or which sheet you are on when you kick off the code.