Variable Ranges with graphs

[Roonaldez]

Hi,

Below is the code I recorded to draw a graph based on a fixed range of data that is not contiguous. I would like the graph to be drawn using the same start points for each series range, but with a varying number of rows.
The data is always in these columns - B,D, F and I and will always start at row 37, but may stop at any point rather than the row 59 in the example.

Range("B37:B59,F37:F59,D3759,I37:I59").Select
Range("I37").Activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Cost to Connect by Creative").Range _
("B37:B59,D3759,F37:F59,I37:I59"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"Cost to Connect by Creative"
ActiveChart.SeriesCollection(2).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 1
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).AxisGroup = 2
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
End Sub

If anyone is able to assist with entering a range it would be greatly appreciated. I have tried named ranges but without any joy - should I still use a named range?

Thank you for your time and any assistance.

 

[SkipVought]

Hi,

"... but may stop at any point rather than the row 59 in the example."

What is the logic for determining this point?

Is it the LAST populated cell in that range? If so will ALL THREE COLUMNS have data in this LAST ROW every single time, since you stated, that this data range is NOT CONTIGUOUS.


Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Roonaldez]

All three columns will always finish at the same row, and I would always want to go to the last populated cell in the range.

Thanks.

 

[SkipVought]

The last cell in bolumn B is

Dim LstCell as range
Set LstCell = SheetObject.Cells(SheetObject.Cells.Rows.Count, "B").End(xlup)

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Bong]

I think maybe the union method is good for this. It seems you have the drawing ok but you need to make a range object that has some variable parameters. Consider this:

r1=5
r2=22
set rng1=sheet1.Range(cells(r1,"b"),cells(r2,"b"))
set rng2=sheet1.Range(cells(r1,"d"),cells(r2,"d"))
set rng3=application.Union(rng1,rng2)

Now you need only set r2 as an input.


_________________
Bob Rashkin

 

[Roonaldez]

Thank you both for your help, but I am not sure what you are telling me to do...or where to insert the code?

I understand that you are providing a variable range of some type, but what is a union method?

Skip - How do I repeat the xampel you gave me? Do i repeat for each colum with a , between the same code repeated three times? Where does Dim LstCell as range go? Should it be repeated each time? What does it mean?


Sorry for so many questions, but to learn I need to understand fully.

Once again, thank you for your time, it is appreciated.

 

[SkipVought]

Or you could add the chart and then add series, assigning the Values & XValues properties.

FYI:
faq707-4811

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Bong]

Well, here's how I would do it. As Skip said, you need to have some method for determining the last row. For the sake of argument, let's say it's an input box. So first you would "ask" the user to give you the last row number, then convert that text to a number, then define the range, then make your graphs:

strRw=inputbox("enter last row number")
iRw2=cint(strRw)
iRw1=37
set rng1=sheet1.Range(cells(iRw1,"b"),cells(iRw2,"b"))
set rng2=sheet1.Range(cells(iRw1,"d"),cells(iRw2,"d"))
set rng3=sheet1.Range(cells(iRw1,"f"),cells(iRw2,"f"))
set rng4=sheet1.Range(cells(iRw1,"i"),cells(iRw2,"i"))
set rngG=application.union(rng1,rng2,rng3,rng4)
    ActiveChart.SetSourceData Source:=[red]rngG[/red], PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:= _
        "Cost to Connect by Creative"
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).AxisGroup = 1
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).AxisGroup = 2
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers

_________________
Bob Rashkin

 

[Roonaldez]

Thanks Bong, but there is no option/point at which someone could enter a value..I was looking for a method of selecting a start point for the range (fixed) and then referncing as far as the last row that contains data (and could vary)...Which I think Skip was referring to, but I can't see where to place the code and how to repeat it..or if indeed I need to repeat it...

 

[Bong]

iRw1=37
[red]iRw2=sheet1.Range("b65536").End(xlUp).row[/red]
set rng1=sheet1.Range(cells(iRw1,"b"),cells(iRw2,"b"))
set rng2=sheet1.Range(cells(iRw1,"d"),cells(iRw2,"d"))
set rng3=sheet1.Range(cells(iRw1,"f"),cells(iRw2,"f"))
set rng4=sheet1.Range(cells(iRw1,"i"),cells(iRw2,"i"))
set rngG=application.union(rng1,rng2,rng3,rng4)
    ActiveChart.SetSourceData Source:=rngG, PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:= _
        "Cost to Connect by Creative"
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).AxisGroup = 1
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).AxisGroup = 2
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers

_________________
Bob Rashkin

 

[Roonaldez]

Ok, thanks, but this doesn;t seem to be working...Where you have sheet1 would I be right in thinking I need to change to the name of the sheet where the data is held? If so, what is the correct syntax? I tried the sheet name enclosed in " but that also failed?
The error code I get is 'Method Range' of object'_worksheet' failed

thanks

 

[SkipVought]

I do not often code the creation of charts on the fly.

More than often, I create the chart, using the Chart Wizard.

HOWEVER, my data ranges are OFTEN Dynamic Range Names. I use a number of techniques including the OFFSET function in the Insert > Name > Define Name Window, and Insert > Name > Create - Createn names in top row or the Worksheet_Change event on VBA.

This I assign the Range Name to the Values and Category series in the Chart > Source Data - Series Tab.

VERY LITTLE VBA CODE.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Bong]

In your original post you had:
ActiveChart.SetSourceData Source:=Sheets("Cost to Connect by Creative").Range ...

Assuming that worked (and you didn't say that it did not) I think
set rng1=Sheets("Cost to Connect by Creative").Range(cells(iRw1,"b"),cells(iRw2,"b")), etc. should work, too.

_________________
Bob Rashkin

 

[Roonaldez]

I've made the change as you recommended but the code stops at

ActiveChart.SetSourceData Source:=rngG, PlotBy:=xlColumns

I've looked at the original code, and see no differences?


iRw1 = 37
iRw2 = Sheets("cost to Connect by Creative").Range("b65536").End(xlUp).Row
Set rng1 = Sheets("Cost to Connect by Creative").Range(Cells(iRw1, "b"), Cells(iRw2, "b"))
Set rng2 = Sheets("Cost to Connect by Creative").Range(Cells(iRw1, "d"), Cells(iRw2, "d"))
Set rng3 = Sheets("Cost to Connect by Creative").Range(Cells(iRw1, "f"), Cells(iRw2, "f"))
Set rng4 = Sheets("Cost to Connect by Creative").Range(Cells(iRw1, "i"), Cells(iRw2, "i"))
Set rngG = Application.Union(rng1, rng2, rng3, rng4)
ActiveChart.SetSourceData Source:=rngG, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"Cost to Connect by Creative"
ActiveChart.SeriesCollection(2).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 1
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).AxisGroup = 2
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers


Thank you for your patience and help!

 

[Bong]

Sorry, I overlooked the part of your original code where you instantiate the chart:
Charts.Add
ActiveChart.ChartType = xlColumnClustered

That has to be before: ActiveChart.SetSourceData Source:=rngG, PlotBy:=xlColumns

_________________
Bob Rashkin