Sleeper: Variable Range Issue

[c7015]

Hey Guys , I'm trying to create a graph from a range of cells that is variable.


I have my macro set to create a chart giving a certain range , however I want excel to stop the range when it comes to a blank cell


here is my code to create the chart

ElseIf optionbutton1.value = True And optionbutton3.value = True And optionbutton4.value = True And Optionbutton5.value = True And optionbutton6.value = True Then
  Range("C20:C121").Select
    Range("C20:C121,E20:E121").Select
    Range("E20").Activate
    Range("C20:C121,E20:E121,F20:F121").Select
    Range("F20").Activate
    Range("C20:C121,E20:E121,F20:F121,G20:G121").Select
    Range("G20").Activate
    Range("C20:C121,E20:E121,F20:F121,G20:G121,H20:H121").Select
    Range("H20").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range( _
        "Calculations!$C$20:$C$121,Calculations!$E$20:$E$121,Calculations!$F$20:$F$121,Calculations!$G$20:$G$121,Calculations!$H$20:$H$121")

but I want the end range value to be the last cell with data

like this

ElseIf optionbutton1.value = True And optionbutton3.value = True And optionbutton4.value = True And Optionbutton5.value = True And optionbutton6.value = True Then
  Range("C20:xldown").Select
    Range("C20:xldown,E20:xldown").Select
    Range("E20").Activate
    Range("C20:xldown,E20:xldown,F20:xldown").Select
    Range("F20").Activate
    Range("C20:xldown,E20:xldown,F20:xldown,G20:xldown").Select
    Range("G20").Activate
    Range("C20:xldown,E20:xldown,F20:xldown,G20:xldown,H20:xldown").Select
    Range("H20").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range( _
        "Calculations!$C$20:$xldown,Calculations!$E$20:$xldown,Calculations!$F$20:$xldown,Calculations!$G$20:$xldown,Calculations!$H$20:$xldown")

but something is wrong because I cant use xldown in this context ...

anyone know how I can accomplish what I am looking to do.

[CatDaddy]

could use something like this?

Range("C20:C" & Range("C65000").End(xlup).Row).Select

[c7015]

could use something like this?
[VBA]Range("C20:C" & Range("C65000").End(xlup).Row).Select[/VBA]

is that just for the c columns , how do I add multiple columns in there

[CatDaddy]

is that just for the c columns , how do I add multiple columns in there

that depends, do the other columns end at the same row as C does?

if so you could do something like this:

Dim r as long
r = Range("C65000").End(xlup).Row
Range("C20:C" & r & ",E20:E" & r & ",F20:F" & r & ",G20:G" & r & ",H20:H" & r).Select

[c7015]

that depends, do the other columns end at the same row as C does?

if so you could do something like this:

Dim r as long
r = Range("C65000").End(xlup).Row
Range("C20:C" & r & ",E20:E" & r & ",F20:F" & r & ",G20:G" & r & ",H20:H" & r).Select

Well there are 6 columns and 121 cells from C:21,c121 h:21h,121 that will make the potential range , however depending on what check boxes are selected determines what columns are included in the range to graph and depending on the age of the person determines how many rows will be involved ( older the person the fewer rows )

The other columns could end at the same row as c , but ideally they would end at the colum with the longest row

I'll have to play more tomorrow at work but hopefully you get the idea of what I am trying to accomplish