Excel 2010 chart 'Switch Row/Column' if data rows > Horizontal Axis entries

[PWD]

Good afternoon, I have inherited a workbook that produces charts for countries. Within each country there are a (known) variable number of regions. There is code that will populate the chart from a range of data that will have as many rows as regions per country. This works fine until I have more rows than I have columns, e.g. for a quarterly chart I have 4 columns that populate the horizontal axis. The code is great for up to 4 regions. As soon as there are more than 4 regions the data are swapped and the regions become the horizontal axis and the quarters become the legend entries.

This is the code

Dim MyPlace As Integer
Dim Mytest As String
'Range("CityLengthsQ") is a range containing the lengths of the region cells to determine 0 length
Mytest = "0"

        MyRangeCount = Range("CityLengthsQ").Rows.Count
        MyRange = "CityLengthsQ"

        For x = 1 To MyRangeCount
        If InStr(1, Range(MyRange).Rows(x), Mytest) And InStr(1, Range(MyRange).Rows(x), Mytest) < 2 Then GoTo line10
        Next x
        
line10:

MyPlace = x - 1 'x goes to +1 so need to subtract 1

MyChartrange = 24 + MyPlace 'Row 24 contains the Quarter labels
Sheets("Chart- City - (Quarter)").Visible = True
    Sheets("Chart- City - (Quarter)").Select
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Source - Brand (Q) (City)").Range( _
        "B24:F" & MyChartrange)

If the chart has been produced with < 5 data rows and I manually edit the Data Source it retains the correct format, it just appears to be when it's done by code.

I've added this to the code after

ActiveChart.PlotArea.Select

    If MyPlace > 4 Then
    
    For p = 1 To MyPlace
    
    ActiveChart.SeriesCollection(p).Name = "='Source - Brand (Q) (City)'!$B$" & 24 + p
    ActiveChart.SeriesCollection(p).Values = "='Source - Brand (Q) (City)'!$C$" & 24 + p & ":$F$" & 24 + p
    Next p
    End If

But that will only work for an existing number of data rows. Grrrr.

I'm sure I'm close but.......

Many thanks,
D€$

 

[PWD]

Crude but this seems to work:-

Dim MyPlace As Integer
Dim Mytest As String
'Range("CityLengthsQ") is a range containing the lengths of the region cells to determine 0 length
Mytest = "0"

        MyRangeCount = Range("CityLengthsQ").Rows.Count
        MyRange = "CityLengthsQ"

        For x = 1 To MyRangeCount
        If InStr(1, Range(MyRange).Rows(x), Mytest) And InStr(1, Range(MyRange).Rows(x), Mytest) < 2 Then GoTo line10
        Next x
        
line10:

MyPlace = x - 1 'x goes to +1 so need to subtract 1

MyChartrange = 24 + MyPlace 'Row 24 contains the Quarter labels
Sheets("Chart- City - (Quarter)").Visible = True
    Sheets("Chart- City - (Quarter)").Select
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    
    If MyPlace > 4 Then
    
    For p = 1 To MyPlace
    
    SeriesCount = ActiveChart.SeriesCollection.Count
    
    If SeriesCount < MyPlace Then
    For sc = SeriesCount + 1 To MyPlace
    ActiveChart.SeriesCollection.Add Source:=Worksheets("Source - Brand (Q) (City)").Range("C24:F24")

    Next sc
    End If
    
    ActiveChart.SeriesCollection(p).Name = "='Source - Brand (Q) (City)'!$B$" & 24 + p
    ActiveChart.SeriesCollection(p).Values = "='Source - Brand (Q) (City)'!$C$" & 24 + p & ":$F$" & 24 + p
    Next p
Exit Sub
    End If
    ActiveChart.SetSourceData Source:=Sheets("Source - Brand (Q) (City)").Range( _
        "B24:F" & MyChartrange)

Suggestions for improvement gratefully accepted

Many thanks,
D€$