sorting errors on repeated sort

[jparker9]

My sort routine produces errors after being used four times. The procedure has been in use for several years with no problems.

Public Sub SortPlayer4()
Range("A11:F154").Select
Range("F11").Activate
Selection.Sort Key1:=Range("F11"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The "F" Column is numeric from formulas and does not contain any blanks

The error is fairly consistant. It moves a row midway down the range to the 17th row where it does not belong. Although this isn't always the case.

I have retyped the formula and sometimes this will allow it to work.
I have copied the formula from a backup copy of the program and this sometimes solves the problem. But it always comes back.

I have reinstalled Excel, but still have the problem.

I have the program installed on three computers and they all produce the same error after about 4 repititions.

In all cases the data is constantly being changed via cuting and pasting so I don't think it is in the data itself.

Any ideas will be appreaciated.

 

[VoG]

Hello and welcome to MrExcel.

Perhaps it should be

Public Sub SortPlayer4()
Range("A11:F154").Sort Key1:=Range("F11"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[jparker9]

entered it your way with the same error. in both cases it works 4 times and fails on the 5th.

 

[VoG]

Fails with which error?

 

[jparker9]

no error message, just doesn't sort correctly

 

[wigi]

Hello

Try using Header:=xlYes or Header:=xlNo, depending.

Wigi

 

[crimson_b1ade]

how 'bout the data in the other columns (A - E) are there 'blanks' there?

The "F" Column is numeric from formulas and does not contain any blanks

 

[jparker9]

Tried your idea re header. I think it is working. I have only run two test, but both were perfect. Will test more and get back to you. Fingers are crossed. Thanks a million

 

[jparker9]

ran program 100 times using the correction in Header:=xlNo and the program sorted correctly every time.

thanks again for our help