Automatically selecting the new data for charts (dynamic arrays, or indexing)

rh8998

New Member
Joined
Jun 10, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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. I know that you can use a table to select a larger area then you need and it will grow the graph with it however those cells that i would select already have a bunch of equations in them waiting for the new data to get pulled i. once the data is pulled in these equations are automatically run and the data is ready to go.

I am more familiar with the program MatLab. where you could tell it something like CS is equal to a specific cell. then in the code you could say that your range is something like RangerRick =(Q4,U(CS)). Where this would know that i am talking about cell U with the number that CS is equal to. I am just not sure how to do it in excel.

Below i have the code that i am playing with. with some comments in it to try and explain what i am doing.

Sub check()
'
' check Macro
'
'
Dim CS As Integer ‘ Defining that CS is the value that I am pulling out of cell AQ which is the indexing number i need like i talked about
Dim RangerRick As Range ‘ defining the range that I am going to make the size of the data

Sheets("EW1060").Select ‘ calling the sheet I want to start with
CS = Range("AQ3") ‘defining what CS is equal to, up to this point the code works right
ReDim RangerRick("Q4", CS) ‘ the next 4 lines are all various attempts at trying to define my dynamic array or indexing the cells this is where i really need the help
' RangerRick = $Q$3:INDEX($Q:$U,COUNTA($Q:$Q))
' RangerRick = Range("RangerRick").Resize(,)
' RangerRick = Range("Q4", Cells(21, CS))
' MsgBox (CS) ‘ just a quick check to make sure CS was the right value
ActiveSheet.ChartObjects("Chart 1").Activate ‘ selecting the chart on the sheet
ActiveChart.PlotArea.Select ‘ next 3 lines are redefining the data that the chart is going to use
Application.CutCopyMode = False
ActiveChart.SetSourceData Source:=RangerRick ‘ it should be noted that I have not made it this far to see if just using the name to define the data is sufficient
'Sheets("EW1061").Select ‘ selecting the next sheet to work on. This should grab the data on this sheet in the same location as the others and then just repeat
'ActiveSheet.ChartObjects("Chart 1").Activate
' ActiveChart.Axes(xlCategory).MinorGridlines.Select
' Application.CutCopyMode = False
'ActiveChart.SetSourceData Source:=RangerRick

End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,304
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

Will something like this work for you?

ABCDEFGHIJKL
1xyDataRange
211StartRow4
324EndRow13
439
5416
6525
7636
8749
9864
10981
1110100
1211121
1312144
1413169
1514196
1615225
1716256
18
19
20
21
22
23
Sheet1


graph.png


MyX: =INDEX(Sheet1!$A:$A,StartRow):INDEX(Sheet1!$A:$A,EndRow)
MyY: =INDEX(Sheet1!$B:$B,StartRow):INDEX(Sheet1!$B:$B,EndRow)

with Graph values:

y Values.png
x values.png
 

rh8998

New Member
Joined
Jun 10, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am assuming that this is creating names for data sets in the Name Manager. Is that correct? I have tried that a couple times but i can give it another go. I am relatively new to trying to go this far into excel. By new I mean I have learned all that I have just in the last 3 days so I definitely don't know a lot, before it was just basic equations.

Thats kinda why i was hoping to be able to do it in VBA so that I can more visually see where everything is coming and going to. There has to be a way to do it i am just not sure. Another reason that i am trying to do it this way is because the graphs that i have created already are pretty unique and challenging to create so i was hoping to make the code just grab the new data.

Thank you for the help!
 

Forum statistics

Threads
1,144,421
Messages
5,724,215
Members
422,543
Latest member
Bravo661

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top