Sort VBA Question

[heffo500]

Hi

When I run the below I get an error, it only seems to happen when there is only one row to sort:

lr2 = Range("D" & Rows.Count).End(xlUp).Row
Range("D1:N" & lr2).Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add2 Key:=Range("N1:N" & lr2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("D1:N" & lr2)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Does it fall over because I only have one row to sort?

 

[footoo]

What error message and what line of code?

Try putting :

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

After :

Range("D1:N" & lr2).Select

 

[heffo500]

Thanks for the reply the error appears at .Apply

The error is:

Run-time error '1004':

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.

 

[footoo]

Try changing to :

Worksheets("Data").Sort.SortFields.Add2 Key:=Worksheets("Data").Range("N1:N" & lr2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Make sure the "Data" sheet is the active sheet.

 

[heffo500]

Try changing to :

Worksheets("Data").Sort.SortFields.Add2 Key:=Worksheets("Data").Range("N1:N" & lr2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Make sure the "Data" sheet is the active sheet.

I see to get a different issue in my code first:

Sheets("

Sheet1​

").Select

lr = Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E1:E" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("

Sheet1​

").Sort
.SetRange Range("A1:R" & lr)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E1:E" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

This line seems to have the issue with Run time error '1004': Application-defined or object-defined error.

When I run the macro it works but after about the third time running this error appears.
​

 

[footoo]

Did you try making the changes per posts #'s 2 and 4 ?

If still not working, I don't know - perhaps someone can help.

 

[heffo500]

thanks for help, after restarting the PC the errors seemed to disappear. I'm testing a lot to try repeat the error but it hasn't reoccured