Forum Discussion
Automatically selecting the new data for charts (dynamic arrays)
I have an excel sheet that automatically pulls in the new data that i want and adds it to the next available row. I have several charts that are associated with this data and right now i have to go through and select each data set individually. which is a pain. I am trying to create a few lines of code that will automatically resize the data that is grabbed. I have the data that starts on Q4 and i want it to go to U, to what ever the value of the integer is that i tell it.
Dim CS As Integer ' this is my last point of data
Dim RangerRick As Range 'this is my array size
Sheets("EW1060").Select ' the sheet i am starting on
CS = Range("AQ3") ' the integer that will define the size of the array
ReDim RangerRick("Q4", CS) ' the following several lines are my attempt at trying to get it to define the data size
' RangerRick = $Q$3:INDEX($Q:$U,COUNTA($Q:$Q))
' RangerRick = Range("RangerRick").Resize(,)
' RangerRick = Range("Q4", Cells(21, CS))
' MsgBox (CS) ' my check to make sure CS was grabing the right value
ActiveSheet.ChartObjects("Chart 1").Activate ' then the next several lines are redoing the graph with the new data I know this part works if i manually put in a number.
ActiveChart.PlotArea.Select
Application.CutCopyMode = False
'Sheets("EW1061").Select ' from here forward i want it to take the already defined data size and use it to change a chart across several other sheets
'ActiveSheet.ChartObjects("Chart 1").Activate
' ActiveChart.Axes(xlCategory).MinorGridlines.Select
' Application.CutCopyMode = False
'ActiveChart.SetSourceData Source:=Range("Q3:U12")
End Sub
ActiveChart.SetSourceData Source:=RangerRick
3 Replies
If you can change the data source of the charts to a table, you won't need any code.
Another option is to create dynamic named ranges for the x-values and y-values.
I agree that those are ways to try and do it with a table. The Issue i am running into is that for most of the cells that i would need to include into the table have equations already in them. these cells are pre-allocated with the equations so that when it pulls in the new data all the calculations are automatically done. The problem with the table method is that it doesn't grab just the information that I want at this time to be in the graph. which is why i am trying to create an index system to select appropriately.
If a table isn't suitable, try dynamic named ranges.
