Build a chart without knowing the exact the end of the table?

[marlonsaveri]

I have a table to build a chart. However, I don't know how many columns it has. It begins with the name in "A" and goes B, C, D... Depends on what's in each row. So, I don't even know exactly which column is filled.

For example:
Prop. |_A_|_B_|_|C_|_D_|_E_|_F_|_G_|_H_
NY___| 12.7| 13
CA___| 13 | 12
OH___|
TE___| 15 | 22 | 25 | 27 | 32 | 11
FL___| 0 | | 13 | | 15
In this case, the chart is from A2 (NY) to G6 (because there's a "11" in G5)

I tried the following code, but the program didn't accept to use:

.SetSourceData = Sheets(Sheets.count).Range("A2").CurrentRegion

And I can't use .End(xltoright) because it depends on each row

Sheets(Sheets.count).Range("A2").CurrentRegion.Select
    Sheets(Sheets.count).Shapes.AddChart.Select
    Selection.Width = 1000
    Selection.Height = 500
    With ActiveChart
        .ChartType = xlColumnClustered
        .SetSourceData = Sheets(Sheets.count).Range("A2").CurrentRegion
        .Legend.Delete
    End With

[6StringJazzer]

I am having trouble understanding what result you want.

Do you want a chart for each row, or all rows plotted on one chart?

Do you know the potential maximum number of columns?

Why are you using VBA to create the chart(s)?

If you create a chart using all data, the blank cells won't be plotted and won't show up on the chart anyway.

There are methods of creating charts that adjust dynamically to the data without using VBA, if that happens to be what you're trying to accomplish.

[romperstomper]

In what way did:

.SetSourceData = Sheets(Sheets.count).Range("A2").CurrentRegion

fail?

[marlonsaveri]

6StringJazzer,
all rows plotted on one chart.
I'm using VBA because the table is made from a VBA code, taking values from many sheets.
So, could I use all cells and blank's will be igored? Thanks

Romperstomper,
Yes, it failled, because I've used " .SetSourceData = ", but it must be: ".SetSourceData Source:="

So, I think now it's running.

[6StringJazzer]

all rows plotted on one chart.
I'm using VBA because the table is made from a VBA code, taking values from many sheets.
So, could I use all cells and blank's will be igored? Thanks.

Is it possible to post your workbook?

Are you building the sheet from scratch each time, or updating existing data? If you define a chart one time, and are using the same area for the data, the chart can be set up to update according to whatever data is there. Hard to be more specific without seeing the chart you want.

[romperstomper]

Oh yes - I overlooked the = sign! (you don't actually need to use the argument name, you could simply remove the = sign, but it's more explicit to name the argument)

[marlonsaveri]

I made a sample.

Click in "Search City" and write 001 and ny to test the form that find informations.
Click in some item on combobox to see the form that should give us the max, min and the average of some property. It works to "Demonym" but it don't works to "Metro Density", for example.

Use the other button to open the charts form in sheet "main"
Test with IDH (=HDI) and Density to see some examples.

So, why it's not working yet?
Thanks very much.