multiple charts - same worksheet - non contiguous cells - VBA code

[9davros8]

hi

problem - I have found some code on the net that basically does what I want. I run the code and it loops through my data on sheet1 and outputs charts on sheet2. however the code gets the source data only from each row and does not allow for non contiguous data. my X axis data is always row 1. the code below works but only gets source data from one row

chrt. Set SourceData Source := .Range (.Cells (I,1), .Cells (I, LastColumn))

so the generated charts picks out its source data s A2: to LastColumn. Then A3: to LastColumn (I to LastColumn) and so on until the last row of data is reached. the charts are generated but the source data is wrong as the data is non contiguous. if my data was static and non contiguous the code below is an example of what i need bt am having problems with:

chrt.SetSourceData Source:= . Rage ("A1:AD1, A4AD4")

so the first code above is partly what I want but i need something like below but my code does not work

chrt.SetSourceData Source:= .Range (.Cells (I, 1): (I, LastColumn), (.Cells (1,1): (1, Lastcolumn)) (

any help appreciated

 

[pgc01]

Hi

Your statement has several syntax errors.

If I understand correctly it should be:

    chrt.SetSourceData Source:= _
        Union(.Range(.Cells(I, 1), .Cells(I, Lastcolumn)), .Range(.Cells(1, 1), .Cells(1, Lastcolumn)))

 

[9davros8]

Hi

Your statement has several syntax errors.

If I understand correctly it should be:

    chrt.SetSourceData Source:= _
        Union(.Range(.Cells(I, 1), .Cells(I, Lastcolumn)), .Range(.Cells(1, 1), .Cells(1, Lastcolumn)))

pgc01 - many thanks. just what I needed. I do have some chart title errors but I will try to fix these. your code correction takes me 90% of the way and is greatly appreciated

 

[pgc01]

I'm glad it helped.