PDA

View Full Version : Sleeper: Variable Range Issue



c7015
02-21-2012, 11:58 AM
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$1 21,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:$x ldown,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
02-21-2012, 12:16 PM
could use something like this?

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

c7015
02-21-2012, 01:24 PM
could use something like this?
Range("C20:C" & Range("C65000").End(xlup).Row).Select


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

CatDaddy
02-21-2012, 01:29 PM
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
02-21-2012, 03:43 PM
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