Need VBA code to create dynamic line charts

S

sabha

New Member
Joined
Oct 10, 2015
Messages
21
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 :)
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Set up your data like this. The top left cell must be blank to ensure that Excel correctly parses the data.

MrExcelDataSetUp.png


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:

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
 
Upvote 0
Hi,

So I ran this code but it does not create a line chart. Instead it filters my data. Please help! :)
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Countries
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Q4.14
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Q1.15
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Q2.15
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Q3.15
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Q4.15
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Canada
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]MCA
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]U.S.
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Brazil
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mexico
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]UKI
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]GWE
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Italy
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]CEE
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]France
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Germany
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Iberia
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]MEMA
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Russia
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Korea
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]China
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Taiwan
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Japan
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SEA
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]South Pacific
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Hong Kong
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]India
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Countries
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]3
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]4
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]5
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Canada
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]U.S.
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Brazil
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mexico
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]UKI
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]sfs
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Italy
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]CdsfsdE
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]France
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Germany
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Iberia
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]jo
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Russia
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Korea
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]China
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Taiwan
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Japan
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]po
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]South
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Hong Kong
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]India
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[TD="class: xl68, bgcolor: transparent"]x
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
In that case, I think Jon's code can be modified as follows...

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)[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!
 
Upvote 0
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.
 
Upvote 0
There's no line in that code which prompts the user to specify the location of a table...
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Create Data Base
Replies
2
Views
259
jolivanes
jolivanes
W
  • Question Question
Defined coloring of dynamic diagrams
Replies
0
Views
392
WalmitAal
W
L
  • Solved
Sorting problems...
Replies
4
Views
501
leighhobson89
L
F
  • Question Question
How to show a chart with information from pivot table?
Replies
1
Views
478
Fractalis
F
F
  • Solved
Array formula to show in matrix form data from table?
Replies
7
Views
467
Fractalis
F

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top