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.
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.