Macro to remove previous and add current variable dynamic series in a chart

[Peter Davison]

I have sent this query in before but I'm not sure I have explained it very well.
I have a clustered stacked column chart but the length of the Y series is variable and can be hundreds (X series is static)
I want to see if there is a macro that can remove all the existing series and add the new series however long it is every time I need to change the chart.
My Y series values starts in Cell B7:AH7 with the Series Name in B7 (always the starting Cell).
I think I need the variable to do an end and end up on the row B to see the end row the number of series' needs to go to.

My Chart is called Chart 8 and is in the sheet called Cluster Graph

Can anyone help me with a macro that can do the above?
Your help is really appreciate.
One of the your team did help me with a renaming of Legends in a chart which was really successful.

As always thank you for your time.

 

[BSALV]

Sub Remake_Chart_Sheet1()
     '***************************************************************************
     'the chart, you have in sheet1, delete all existing series and add the actual "in use" series of sheet1
     '***************************************************************************
     Dim cX    As Range

     Set sh = Sheets("sheet1") 'this sheet

     Set chrt = sh.ChartObjects(1).Chart '1st chart in that sheet (there is only 1 for the moment)
     With chrt
          
          For i = .FullSeriesCollection.Count To 1 Step -1
               .FullSeriesCollection(i).Delete 'delete all existing series
          Next

          Set cX = sh.Range("C4:V6") 'the values for the X-axis (thus also the number of columns)
          
          For Each c In sh.Range("B7:B205").Cells 'loop through this cells
               If Len(c.Value) > 0 And c.Value <> 0 Then 'if they have a non-zero value then it's a new serie
                    With .SeriesCollection.NewSeries
                         .Name = "='" & sh.Name & "'!" & c.Address 'the name
                         .XValues = "='" & sh.Name & "'!" & cX.Address 'x-range
                         .Values = "='" & sh.Name & "'!" & c.Offset(, 1).Resize(, cX.Columns.Count).Address 'values
                    End With
               End If
          Next
  
     End With
     
     DoEvents
     MsgBox "still with a mixture of colors"
        
     With_New_Colors chrt 'give the series a user defined color
     
End Sub

 

[Peter Davison]

As communicated -
Thank you for all the help, I think I have most working.
Is it possible in the code above to just make it have prime clear colours as the default then there will be little need to probably change colours.
Just colours that are easy to read the data labels
and also switch data labels on automatically?

There were a couple of changes to the above code -
Sheet1 will be Cluster Graph
X Axis Range will be C4:AH6
Y Axis Range will be B7:B1500

Appreciate your help