[Excel] Changing Chart Series Order

Here is a Bar Chart with Series 1 to 6. (bottom-up)

The data table is as shown below

If the series order required is: 5, 1, 3, 2, 6, 4 then enter the series names inside the code and select the chart and run the macro. The series order will be changed accordingly.

'--------------------------------------------------------------- 
'This macro changes the order of series in a collection of series 
'of a chart. The series names has to be hard coded inside this 
'code. 
'For this macro to work property 
'1. A chart should be selected 
'2. It should have more than 1 series 
'Copyright vbacentral.blogspot.com 
'--------------------------------------------------------------- 

Sub OrderFeesSeriesOfActiveChart()
 
  Dim sc As SeriesCollection
  Dim s As Series
  Dim LookupArray(6, 2) As Variant 
  Dim SeriesNameArray() As Variant 
  Dim OrderArray() As Variant 
  Dim Temp(1, 2) As Variant 
  Dim str, str1 As String 
  Dim SeriesCount, Count As Integer 
 
  Set sc = ActiveChart.SeriesCollection
  
  ' Count the number of series in the selected chart 
  Count = 0
  For Each s In sc
    If Trim(s.Name) <> "Total" Then Count = Count + 1
  Next s
  
  SeriesCount = Count
  
  ReDim SeriesNameArray(SeriesCount)
  ReDim OrderArray(SeriesCount, 2)
  
  ' Create 2x2 Lookup array with order wanted. 
  ' Hardcode the series names according to the desired order
  LookupArray(1, 1) = "Series 5"
  LookupArray(2, 1) = "Series 1"
  LookupArray(3, 1) = "Series 3"
  LookupArray(4, 1) = "Series 2"
  LookupArray(5, 1) = "Series 6"
  LookupArray(6, 1) = "Series 4"
  LookupArray(1, 2) = 1
  LookupArray(2, 2) = 2
  LookupArray(3, 2) = 3
  LookupArray(4, 2) = 4
  LookupArray(5, 2) = 5
  LookupArray(6, 2) = 6
 
   'Store series names in an array 
    For k = 1 To UBound(SeriesNameArray())
      SeriesNameArray(k) = Trim(sc(k).Name)
    Next k
    
   'Lookup series order from Lookup array 
    For i = 1 To UBound(LookupArray())
        For j = 1 To UBound(SeriesNameArray())
          If LookupArray(i, 1) = SeriesNameArray(j) Then 
            OrderArray(j, 1) = LookupArray(i, 1)
            OrderArray(j, 2) = LookupArray(i, 2)
            End If 
        Next j
    Next i
    
   'Sort the elements in OrderArray using order obtained from Lookup array 
    For i = 1 To SeriesCount - 1
      For j = i + 1 To SeriesCount
        If OrderArray(i, 2) > OrderArray(j, 2) Then 
          Temp(1, 1) = OrderArray(j, 1)
          Temp(1, 2) = OrderArray(j, 2)
          OrderArray(j, 1) = OrderArray(i, 1)
          OrderArray(j, 2) = OrderArray(i, 2)
          OrderArray(i, 1) = Temp(1, 1)
          OrderArray(i, 2) = Temp(1, 2)
        End If 
      Next j
    Next i
  'Assign new series order 
 
  For Each s In sc
      For i = 1 To SeriesCount
          If Trim(s.Name) = OrderArray(i, 1) Then 
              s.PlotOrder = i
          End If 
      Next i
  Next s
 
End Sub