Need VBA code to create dynamic line charts

[sabha]

Hi so this is my chart and x stands for a number (BTW my table has more than this many columns and rows):

Countries 1 2 3 4 5
AMS
Canada x x x x x
U.S. x x x x x
Mexico x x x x x

Europe
Italy x x x x x
France x x x x x
Germany x x x x x
Russia x x x x x

APP Korea x x x x x
China x x x x x
Japan x x x x x
Hong Kong x x x x x
India x x x x x

I want to create a dynamic line chart using VBA for every region. For example, when I highlight the range from Canada to Mexico, I want a line chart that will include all the country names in the legend and the numbers 1,2,3,4,5 will be by horizontal axis. Now when I highlight the range from Italy to Russia, I want to do the same thing but for those countries. Same goes for the countries under APP. The only thing common among all charts should be the horizontal axis of 1,2,3,4,5. Please help! Thanks

 

[Jon Peltier]

Set up your data like this. The top left cell must be blank to ensure that Excel correctly parses the data.

The following lets you be a little sloppy with your data selection. If you accidentally select two columns, not just the country names, or if you accidentally select the second column instead of the first, it will get the data you want. Set the value of the constant nCOLS to the number of columns including values and country names, so 6 in our case.

Select the data you want and run this code:

Sub InsertLineChartSelectedCountries()
  ' number of columns including names and values
  Const nCOLS As Long = 6
  
  Dim rData As Range
  
  Set rData = Selection.EntireRow.Resize(, nCOLS)
  
  Set rData = Union(rData, rData.EntireColumn.Resize(1))
  
  With ActiveSheet.Shapes.AddChart(xlLineMarkers).Chart
    .SetSourceData rData, xlRows
  End With
End Sub

 

[sabha]

Hi,

So I ran this code but it does not create a line chart. Instead it filters my data. Please help!

 

[sabha]

Countries Q4.14 Q1.15 Q2.15 Q3.15 Q4.15 Canada x x x x x MCA x x x x x U.S. x x x x x Brazil x x x x x Mexico x x x x x UKI x x x x x GWE x x x x x Italy x x x x x CEE x x x x x France x x x x x Germany x x x x x Iberia x x x x x MEMA x x x x x Russia x x x x x Korea x x x x x China x x x x x Taiwan x x x x x Japan x x x x x SEA x x x x x South Pacific x x x x x Hong Kong x x x x x India x x x x x <tbody> </tbody> Countries 1 2 3 4 5 Canada x x x x x U.S. x x x x x Brazil x x x x x Mexico x x x x x UKI x x x x x sfs x x x x x Italy x x x x x CdsfsdE x x x x x France x x x x x Germany x x x x x Iberia x x x x x jo x x x x x Russia x x x x x Korea x x x x x China x x x x x Taiwan x x x x x Japan x x x x x po x x x x x South x x x x x Hong Kong x x x x x India x x x x x <tbody> </tbody>
So this is what my table looks like and I can't change it! I want to make multiple charts and each chart will have countries from the same region.

<tbody>
</tbody>

 

[sabha]

idk why there are two tables, but I only have one table

 

[Domenic]

The code seems to work for me. I selected cells A2:A4, which contain Canada, MCA, and U.S. Then I ran the macro, which created a line chart based on those three rows. Note Jon said that the top left cell must be blank. However, in your last sample data, this isn't the case. Cell A1 contains the text "Countries". Although, in my testing, the code seems to run fine even with it there.

 

[sabha]

okay so for me, columns A-C contain something else that I can't change. For me cell D2 says "Countries" and row 3 and after are the countries.

 

[Domenic]

In that case, I think Jon's code can be modified as follows...

Sub InsertLineChartSelectedCountries()
  ' number of columns including names and values
  Const nCOLS As Long = 6
  
  Dim rData As Range
  
  Set rData = Selection.EntireRow.Resize(, nCOLS)[COLOR=#ff0000].Offset(, 3)[/COLOR]
  
  Set rData = Union(rData, rData.EntireColumn.Resize(1)[COLOR=#ff0000].Offset(1)[/COLOR])
  
  With ActiveSheet.Shapes.AddChart(xlLineMarkers).Chart
    .SetSourceData rData, xlRows
  End With
End Sub

Hope this helps!

 

[sabha]

That still doesn't work A pop up asks "where is the data for your table" and then when I click ok, it just makes the selected rows into a table with filtered headers.

 

[Domenic]

There's no line in that code which prompts the user to specify the location of a table...