# Dynamic Range Selection For Charting

May 7, 2009

I have three rows that each attempt to pull in data from a range on different worksheets. All but one return nothing but "#N/A" values. The one row returns values from the proper range. (User selections determine which row will have data).

Second, I successfully pull data from the row containing actual information into another row, using the following formula: "=OFFSET(E\$36,CHOOSE(\$Y\$1,0,1,2),0)" on a cell-to-cell basis. The value in \$Y\$1 chooses the row to look at, based upon a dropdown selected on another worksheet.

So I now have a row with =OFFSET(E\$36,CHOOSE(\$Y\$1,0,1,2),0),=OFFSET(F\$36,CHOOSE(\$Y\$1,0,1,2),0),=OFFSET(G\$36,CHOOSE(\$Y\$1,0,1,2),0) and so forth. I can find the last value in the row, but I cannot find a way to extract the address from that, and create a table that will use as a series the last 26 values in the row.

For reference, I use "=LOOKUP(9.99999999999999E+307,E49:FD49)" to determine the last cell with a value (gleaned from a Dueling Excel Youtube Video, - very helpful!).

I hope the above will be clear enough, but if not, I'll be happy to provide additional information / clarification.

## Charting With Dynamic Ranges

Mar 25, 2014

So I was looking for reassurance or validation more than anything. From what I can tell you can in order to build a chart that is dynamic throughout a range, you use the offset and count or counta function - 1. That part isn't a problem. My question is once you created that for your charts do you just normally plot your chart range or do you have to reference the named range directly into the chart range?

## Dynamic Graphing/Charting

Mar 26, 2008

I have a workbook with Sheet1 and Sheet2. I’ve programmed information from to be calculated and results inserted into sheet2. A new result every Row. I plan to do a graph taking the X-axis and Y-axis values from Columns A and B respectively in Sheet2. What is the VBA code for dynamic updating of graph? Meaning, I want the graph (a curve) that will have points added to the curve, extending it, whenever a new row is added into sheet2 – Columns A (X-axis) and B (Y-axis). The rows are not defined. It can have 100 to 200 rows or more..

## Dynamic Range Selection

Feb 8, 2010

I am filtering a list in Column H and depending on what criteria I filter on I need to be able to select only what I have actually filtered for. I am using the code below to find the lower right corner of my range that I'm trying to select and this works great.

## Macro To Run On A Dynamic Selection Range

Oct 13, 2008

I am looking to run a marco for a selection range, and the selection range could vary in size.
Using the Macro recorded (whilst turning on the relative reference) the Macro runs for a defined number of cells. I would like to run certain Macro, for different ranges of cells.

## VBA Dynamic Range Selection Failure

Aug 3, 2009

I have a log that I use to keep track of my hours where the days hours and charge numbers get summed and moved to a new sheet via a command button. One of the categories is week ending date. I want the week ending date cells to merge if they match and cannot get the range I want to highlight. I can move the data, find and define the first and last cells in the range, but can't get the range.select command to work. I think it is just a syntax error, as I am new to VBA.

I can open a Locals window and watch it assign values to myFirstCell and myLastCell of "\$A\$36" and "\$A\$44" respectively, but I get an error on either the set range= or range.select lines.

My code is as follows:

Private Sub Post_Hours_To_Log_Click()
'Posts the day's hours to the hours tracker.

Dim myFirstCell, myLastCell As Variant

Dim myRange As Variant

***Removed N/A code that goes to another sheet and decides which data to copy***
Application.Goto reference:=Worksheets("Hours Log").Range("A2"), _
Scroll:=False

## Dynamic Charting Of Multiple Series Data

Jan 12, 2009

I have several dynamic charts using named ranges, but I am charting 1 series of data. What about when I have multiple data sets or series and do not want to use PivotTables? Do I really have to make each one a named range? If that's the case, using VBA looks much simpler.

## Dynamic Charting Via Dropdown Reference And Offset Function

May 18, 2007

i have 2 worksheets. one with portfolio stock data, and a second that i would like to use for dynamic charting. the chart worksheet has a drop down menu that chages a refernce cell to a number corresponding to the collumn in the portfolio worksheet for the chosen ticker

i would like to make a dynamic chart that updates the data ranges based on something like this: ....

## Copy Data From One Sheet To Another With Dynamic Range Selection?

Feb 21, 2011

The CRM application that we use provides output in an excel sheet "Table View 1". I want to be able to copy the data that is dumped to a sheet to another sheet. The requirements:The data output range is different. Sometimes it is 4 rows and sometimes it is 25 rows. I want a mechanism where based on the output, the formula/macro automatically copies the information to the new sheet.The first 2 rows need to be excluded when the copy is being made.

## How To Only Allow Multiple Cell Selection If Named As Dynamic Range

May 13, 2014

I have a spreadsheet where I've disallowed selection of multiple cells using the code in the 2nd box below, but is there a way to override this protection to allow selection of a named range? My reason for doing so is to open this workbook as read only from code in another workbook and copy the entire table. My code for doing that works ok (I frequently use it in other workbooks) but, in this case, my need to disallow selection of multiple cells in this particular worksheet is tripping me up.

This is the code I'm using to try and pull the data into another workbook:

Code:

Application.GoTo Reference:="JobsTable"
Selection.Copy

and this is the code I have in RSL-Jobs-Register.xls which is preventing me from selecting the (dynamic) named range called "JobsTable".

Code:

''' I'm thinking I could have something here like (in plain English) "If selected range "JobsTable" Then"
''' (apply the code below) but I'm not sure of the code

If Selection.Cells.Count > 1 Then
MsgBox "Sorry, operations on multiple selections aren't allowed here", vbCritical
ActiveCell.Select
Exit Sub
End If

The formula for the dynamic named range called "JobsTable":

=OFFSET(Jobs!\$A\$2,1,0,COUNTA(Jobs!\$A:\$A)-2,11)

## Charting Multiple Dynamic Ranges As Single Continuous Series

Mar 10, 2014

I have a log where I'm recording daily readings of a pipette. Each day three readings are taken and I have each stored as a dynamic range so that I can monitor how in control my process is. However, when I graph it, I would like it to be one continuous line graph (Three readings from day one followed by three readings from day two, etc.). I'm having difficulty doing any sort of combination.

## Charting A Range Of Values

Aug 31, 2007

I have a simple sheet to record and graph my bloodpressure . The list of values is as follows:
col A is always todays date
cols B and C user input - integer
col D is a constant value 120
col E is also a constant value 80

Currently I am manually charting the range A1:Exx each day after I have added a new line for my daily readings. Whilst this works fine, I'd like to automate the charting process as currently if I select a range full of blank cells then my chart displays loads of blank values.

So I could use a Button on the graph which works out how big my cell range is ie; A1:E66 and graphs it for me or alternatively a background macro that does the same after I have entered the C col entry for each day

## Offset And CountA To Auto Select Data Range For Charting Purposes?

Dec 12, 2013

how to play with data ranges and define names for charts to automatically update them by using the formula offset + counta. An example would be: =OFFSET(Sheet1!\$B\$7,0,0,COUNTA(Sheet1!\$B\$6:\$B\$37)-1).

So, the problem comes when there is a need to select a data range which is not that well defined like \$B\$6:\$B\$37. So, I need to import data regularly into my workbook, this data will be imported right below of the last cell with values.

Now, how would I manage to automatically select the data range from this new import for my chart? See attached example. I have highlighted in Yellow a potential import... there is no data in there so feel freel to fill it with any numbers you like.

I have tried to use a vlookup to find Country+Programme+Year (cat1 in the example) in the 'counta' part of the formula above. But I get lost,as a vlookup would retrieve a value and would not be useful for the data range?

## Dynamic Row Selection

Feb 4, 2007

Here is my code piece
================

For pCounter = 3 To OutputWorkbook.Worksheets("abc").UsedRange.Rows.Count

If temp = outputWorkbook.Worksheets(Output). Cells(pCounter, 1) Then

rowToColour = pCounter + 1

outputWorkbook.Worksheets(Output).Rows("rowToColour:rowToColour ").Select

With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If

Next pCounter

-----------
Explaination
-----------
-I store some numeric value in "temp" variable in some other part of my program

- Then i need to search the "Output" sheet to find the Cell in which i have the same value stored as the string "temp"

- And once i have found the cell , i need to colour the row beneath that cell with red.

The issue is with selection and colouring of the row after finding the cell. So the question is :

Is this the right way to select the row ? i keep getting errors like 1004 etc.

## Data Sorted On Dynamic Selection

Aug 22, 2013

I have data that I need to be dynamically sorted in the highlighted manner. The values on the selection criteria are dynamic.

## Dynamic Selection Of Data For Chart

Oct 13, 2009

I have a follwing data:

Sheet1  ABCDEFGH2Days12345673Values2040550000 Excel tables to the web >> Excel Jeanie HTML 4

I have plotted a line graph based on the above table; but I am getting zero values also plotted. since these Values are linked to some other workbook I can't delete the cell values also.
How can we avoid Zero values on a graph; Also is there any approach, that my graph should get automatically updated whenever the linked values are updated.

## Dynamic File Selection And VLookup Function?

Jun 22, 2013

I will receive one master file which contains the table and it's values and I have another working sheet where I will update the require details. Every time I will copy the entire master data into working file in order to extract the values by using vlookup function. note that the master file has different names each time. So, that if I use vlookup by taking master data path but not getting the results.

I am looking one macro code to select file (master data) and paste the values based on vlooup references in workings sheets. Note this master data file name may change but reference range always same.

## How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

## Dynamic Named Range Sort Including Cells Outside Defined Range

Apr 3, 2008

Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!\$A\$2,0,0, COUNTA(DataSource!\$A:\$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

## Creating Named Range Taking Avg To Date Of Dynamic Range

Jul 15, 2014

I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.

## Dynamic Range For Pivot Table :: Error : Range Is Invalid

Oct 28, 2009

I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:

=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))

I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.

## Convert Dynamic Range To Static Named Range

Sep 13, 2007

My searches have not produced anything that I could apply to this situation.

I'm trying to write VBA that would:

1. Search a Workbook for Dynamic Ranges.

2. When a Dynamic Range is found the code would:

A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name \$Column\$Row:\$Column\$Row"

3. Save Changes.

4. Close File.

My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.

## Charting In VBA

Aug 15, 2008

I have an existing chart and I want to update the plot utilizing data that has been calculated and stored in 2 arrays X(N,1) and M(N,1) via my VBA macro. How do I do it?

## Dynamic Range 2003: Run Calculations On This Range

Nov 5, 2009

I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.

## Dynamic Range Used Named Cell Range

Jul 7, 2014

Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.

Range("DataSummary),(??,??) doesn't work.

## Set Range Variable To Growing Dynamic Range

Mar 6, 2008

I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:

Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

## VBA Charting Weirdness

Jul 1, 2006

I am just reusing some code that works most of the time to reset the seriescollection of specific charts. Sometimes is will just error when I try to set the .values object to my range of values. It's strange because it will work for 10 seriescollections/identicle ranges on the same chart and then error out; and sometimes it will work perfectly.

I've checked:
The chart reference object
The range I'm setting
The code I've copied from

It's all correct. Here's a short sample.

ValRng = Range("AR7:CY7")
Set usechart = ActiveSheet.ChartObjects(1)
With usechart.Chart.SeriesCollection.NewSeries
.Name = "Prime 2000"
.Values = ValRng ' (also tried it this way) "=" & ActiveSheet.Name & "!" & Range("AR7:CY7").Address
End With

The series is created fine, and the name is set properly.

On my new sheet here it won't set ANY values, which is a real problem. If anyone knows what is going on, please let me know.

## X Y Charting With Supplemental Information

Mar 26, 2014

I have a large number of files containing thousands of X and Y coordinates. I prepared a macro which loads these X and Y data from all the files into one workbook, and it writes the file names into each row. So, in the end I get a file with this formation:

X Y filename

x1 y1 filename_1
x2 y2 filename_1
x3 y3 filename_1
.
.
.
xi yi filename_2
.
.
xn yn filename_m

It would be great if I could make then an XY Chart which contains the information of "filename" column too. I didn't mean labling, because in the end I'll have hundred thousands of points, so if I added labels to each point it would be impossible to see anything. I would only set that if I move the mouse to any point of the chart, it would show not only the X and Y coordinates, but the related filename too.

Is it possible to set it so in excel?

## Charting Non-numerical Data?

Dec 2, 2013

produce graphs/charts based on inequalities monitoring information - gender, nationality, age etc. I'm capturing info from several events, one sheet per event. Had a look at COUNTIF but not sure it's what I want and looks like I'd need sub-sheet for each? Capture.PNG

## Charting Information Between Two Given Dates

Nov 22, 2011

I am working on a project at work. I need to track our VIP guests. I only have their arrival and departure date.

Is there a way to graph and track the days between so that I can have a total of all arriving and In-house VIP guests on all dates in the range?

Here's a sample of the data.

10/18/201110/19/2011HOERMAN/SCOTT10/17/201110/21/2011SMITH/JOHN M
10/10/201110/13/2011WARNER/AMY10/18/201110/19/2011GERONDALE/RON
10/11/201110/12/2011BUCHANAN/JEFFREY10/17/201110/18/2011HUND/LARRY