VBA code to change source data in excel chart

[pruleone]

Hi,

I recorded with macro recorder VBA code wich will change data range in chart.

Code is like this:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B2:L5"), PlotBy:= _
xlRows
ActiveWindow.Visible = False
Windows("Proov.xls").Activate
Range("A1").Select

Is it possible and if it is, then how to make the code so that it would choose source data to be only so much cells, where are info?

Right now I have that problem, that my excel chart is half empty. I assigned data range B2:AQ2, but some time info is only in B2:Y2, sometime in B2:S2 and sometime in B2:AQ2.
So what I want is that excel would automatically change data range according where is info.

Can someone help me?

 

[pruleone]

Hy,

I found a solutsion without VBA.

Here you can read about it:

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=518

 

[AnotherHiggins]

FYI: No need for VBA. This can be done with Dynamic Named Ranges.

Have a look at faq68-1331.

BTW: If you're using Excel 2007 Dynamic Named Ranges are automatically created when data is stored as a table. If you have 2007, post back and we can help you with that.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kd5pff]

I have searched for this answer high and low and the "non-vba" versions never worked for me.

However I have finally created a solution that ACTUALLY WORKS IN VBA

Sub Macro3()

On Error Resume Next

Dim n As Integer

Sheets("Sheet1").Activate

n = Cells(1, 3)

range(Cells(1, 1), Cells(n, 1)).Select

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.SetSourceData Source:=Sheets("sheet1"), PlotBy:=xlColumns

End Sub


The idea here is that vba can't handle setting cells as a range when using charts. I don't know enough to understand why but it happens. So if you use the “On Error Resume Next” command it ignores that error.

So if you leave the “.range code” out of the ActiveChart.SetSourceData source:=Sheets("sheet1"), PlotBy:=xlColumns portion it will automatically choose any source highlighted. Which happens to be the range(Cells(1, 1), Cells(n, 1)).Select portion of the code.

This allows you to create a chart with an unknown number data points. In this example the number of data points is set by cell 1,3 however this could easily be adapted to run in a loop or to count the number of active cells.

Its took 3 days to find this solution but hopefully it will help someone else as it helped me.

Im sure the other solutions would have worked however in my application I simply couldn’t use them.

Let me know if you have any questions with my code. Or suggestions.

 

[SkipVought]

I have searched for this answer high and low and the "non-vba" versions never worked for me.

John's solution works EVERY TIME, if it is correctly implemented.

Each Name (Value(s) & Caregory or y(s) & x) is assigned in the Chart Wizard, Source Data - SERIES window in a like manner...
[tt]
=TheSheetName!TheRangeName
[/tt]
If you used ONLY the range name, then your attempt will fail.

If your range name is not dynamic, then the range will not chage to reflect added or deleted data points on BOTH axes.

Exactly what did you try that failed?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

I Second Skip here.

When I first tried to do this it took a while to get it right. But once you know how to do it you'll find yourself converting just about every chart you get your hands on.

Including unnecessary VBA increases the file size, causes an alert when you send it to others, and has to be 'fired off' to update the data which, if keyed off certain sheet events, can cause a sheet to respond sluggishly.

Using Excel's native functionality is much more elegant, smoother, faster and easier solution.

If you let us know what trouble you're having I'm sure someone here at Tek-Tips can help you get it working.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.