Weird Sorting Problem

[Alex Piotto]

Ciao brava gente!
I am experiencing a very strange behavior sorting a few columns of data, by date. Real dates, not text.
The script below from the macro recorder seems ok to me.

ActiveWorkbook.Worksheets("HOME").Sort.SortFields.Add Key:=Range("B13"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("HOME").Sort
        .SetRange Range("B13:J10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

The sorting happen, but after several new insertion of rows and data, sorted every time, I get this

full of levels. Tens of them! After saving the file and reopening I get this message:

Never seen before... what is happening?

 

[Fluff]

When using SortFields to need to clear them every time you run the code like

   With ActiveWorkbook.Worksheets("HOME").Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("B13"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
      .SetRange Range("B13:J10000")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With

The other option is to use the Range.sort method instead

With ActiveWorkbook.Worksheets("HOME")
   .Range("B13:J10000").Sort key1:=.Range("B13"), order1:=xlDescending, Header:=xlYes
End With

 

[Alex Piotto]

Well... you are the Man! Thanks
I forgot to close the door at the end...

 

[Fluff]

You're welcome & thanks for the feedback.