Help in creating Excel template that graphs data with variable # of rows?

Question

I am trying to create an Excel template to graph some data I have. The data will be collected every few weeks and then pasted into one worksheet of the workbook. The columns will remain constant, but the # of rows of data will vary. I tried to just select the whole column (for both the x & y data), but then it graphs all of the rows it can fit in a chart, including all the blank rows at the end... Is there a way to say "graph all rows in Columns A&B with values", or select $A:$____. Any help would be appreciated. Thanks!

Answer 1

It sounds like you need a macro for that. If there is no data in Column C the Macro is pretty simple.

First, go to:

View > Toolbars

Then select the control toolbox toolbar to make sure it is visible. After you do that, select the command button on the toolbar and create a command button.

Now right-click on the new command button and select View Code. From where your cursor is located enter the following:

ActiveWorksheet.Range("A1").Activate
ActiveCell.CurrentRegion.Select

Close that window and go back to your regular Excle page. Right click the command button again, but this time select Properties. Change the property for Caption from CommandButton1 or whatever the name of the command button is to whatever you want the command button's text to display. While you're at it change the PrintObject property's value to False. Changing the PrintObject property to False causes the command button not to be printed if you want to print the data on that page without seeing the command button. DO NOT change the Name property at the top of the list. If you do the macro you just wrote won't work. After changing the Caption property, close the Properties window.

Now move and size your command button how you want it. When you have it the way you want it as far as placement and size make sure it is selected and click the button that looks like a drafting triangle on the control toolbox toolbar. This makes the command button active and ready to run.

As long as Column C is left completely blank and as long as you put the command button on the worksheet you want to select the data from, clicking the button will select everything from A1 to the end of your data automatically no matter the length of your data.

Answer 2

Hi;

I am not sure what your looking for. What type of chart are you using. You reference data in x & y so I assume you are using a scatter chart. When I plug in data in 6 cells in both columns a and b and choose the source data to be in columns on the data range tab, it does not chart the blank cells. I have it set to accept from a1 to b6000 and it works fine.

What kind of chart are you trying to use? Some clarification would help to understand what your doing. Be specific about the data and chart type that you would like to apply to that data. Dollars, Decimal values, Dates, Combination of the three or more?

If your data is crossing rows and not columns, excel limits you to 255 data points in an xy scatter chart.

God Bless

Frank Pytel

Answer 3

I don't think you can - if the data is in the table then Microsoft will put it into the Graph. if you don't want it in the Graph - take it out of the table. i don't see any other way to do this.