I was trying to sort my legend and could not find any reliable answer. Here, Reordering Chart Data Series in Excel, you can find out how to do it manually. Also, a code was posted there, which was not working for me and it was too complicated for a task like this. Basically, we are trying to automate what is shown in the following picture.
I am posting this to document my solution to this problem. Please propose your answers or look-up mine below if you have the same question.
This code gets the series names, puts them into an array, sorts the array and based on that defines the plotting order which will give the desired output. You can change ActiveChart to chart name to be more explicit. Feel free to apply any improvement.
Sub Sorting_Legend()
Dim Arr()
ReDim Arr(1 To ActiveChart.FullSeriesCollection.Count)
'Assigning Series names to an array
For i = LBound(Arr) To UBound(Arr)
Arr(i) = ActiveChart.FullSeriesCollection(i).Name
Next i
'Bubble-Sort (Sort the array in increasing order)
For r1 = LBound(Arr) To UBound(Arr)
rval = Arr(r1)
For r2 = LBound(Arr) To UBound(Arr)
If Arr(r2) > rval Then 'Change ">" to "<" to make it decreasing
Arr(r1) = Arr(r2)
Arr(r2) = rval
rval = Arr(r1)
End If
Next r2
Next r1
'Defining the PlotOrder
For i = LBound(Arr) To UBound(Arr)
ActiveChart.FullSeriesCollection(Arr(i)).PlotOrder = i
Next i
End Sub
Excel treats name of the series as text. So you would encounter the problem that instead of having 1,2,3,... you end up getting 1,10,11,...,19,2,20,.... In that case, convert the array to number. There are questions here, like convert an array to number, that would do the job. You can also simply compare Cdbl of each element with the one of the other. (i.e. If Cdbl(Arr(r2)) > Cdbl(rval) Then ...)