How to explain what columns must be used in a chart?

[marlonsaveri]

The excel creates a table and a chart using macros. Only a two columns chart (Name and Value). However, if there is numbers in Name columns, then instead of it create just one series (names on X axis and values on vertical bars), it created two bars (one to each column) and puts any natural number (1,2,3...) on x axis.

1. I tried to format the first column as "text", but it not resolved.
2. I recorded a macro like this:

Columns("E:E").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source: = Range ("'Sheet1'! $ E: $ E")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection (1). XValues ​​= "= 'Sheet1'! $ D: $ D"

However, it doesn't asoociate values with names if we have a empty row or a title. Exemple:

abc 1
def 123
312 124
Ax1 4

returns a chart when ABC is the title; "1" is associated to "def", (...) and 4 is associated to nothing.

 

[marlonsaveri]

It helps:

with activechart
.SetSourceData Source:=sheets(1).Range("B1", Range("B1").End(xlDown).Address)
.SeriesCollection(1).XValues = sheets(1).Range("A2", Range("A2").End(xlDown).Address)