Home » MS Office

Excel Chart - Dynamic named range skips data



I have a dashboard in Excel which includes some charts. For one the number of rows of data changes regularly so I wanted to use a dynamic named range to refresh the data when a user opens the particular sheet.


This has worked, and the chart picks up the category names and all of the data except for the first column. It picks up the series name and shows this in the legend but does not show any data. Does anyone know why this is?


The VBA script is;


Sub Activate_Custody()


ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = CustodyData


End Sub


The named range CustodyData is set using the following formula;



This highlights the correct area of the worksheet, picks up the category names in the first column, and the correct number of rows in the data table.


I don't get any error messages, and all the data updates except the first series.


7 Answers Found


Answer 1

Unless you have defined a variable to reference the named range then your current variable, CustodyData, is an empty variant.

Try this,

ActiveChart.SeriesCollection(1).Values = "='" & thisworkbook.name & "'!CustodyData"


Answer 2

Hi Andy,


Thanks for the reply, unfortunately your suggestion generates an error.


Runtime error 1004

Unable to set the values property of the series class.


Using just the Name the chart is picking up the category labels, series names and data from 4 out of 5 series, so it appears to be able to read the variable.


Its just missing the first column of data, which seems bizarre as this is between the column with the category names in and the 4 columns of data it does pick up. As its picking up the series name, for the blank series, correctly I have to assume that it is picking up the correct range but for some reason is failing to read the data in that range?


Answer 3

You do not show in your code where or how you assign the variable CustodyData to the named range.

Do the cells covered by the named range contain error values or are empty?



Answer 4

Maybe you have to explicitly refer to the named range (and I interpreted the OFFSET function as the named range CustodyData) as in what Andy wrote, except maybe

ActiveChart.SeriesCollection(1).Values = "='" & thisworkbook.names("CustodyData") - and do we need to convert thisworkbook.names("CustodyData") to a string?

Otherwise, I'm wondering if the chart is picking up the first column as the x axis values...

hope this helps,




Answer 5

I tried a number of different ways to assign the variable (e.g. using ActiveWorkbook.Names("CustodyData") etc) but everything I tried generated error messages. When I left it just as the Name, it worked except for skipping the first column of data.


None of the cells contain error values, and categories with no data are displayed as zeros.


To be honest I'm starting to think that the problem is actually with the chart or the data itself. As I tried refreshing the data by manually setting the series in the Source Data window and it still dropped the first column of data?


Thanks for the help with this, but I think I'm going to start from scratch with this one. I'll let you know if I ever find out what the problem was.


Answer 6

If you end up back at the same problem it my help if you can post an example workbook on a free download site, such as skydrive.



Answer 7

The problem seemed to be with the SeriesCollection code, it appears putting SeriesCollection(1) excluded the first series for some reason. I confirmed this by trying SeriesCollection(2) and SeriesCollection(3), these skipped the first 2 series and first 3 series respectively. Not sure how/why that happened, I must have missed something.


I changed the code completely after that, and set the range within VBA itself now. I've used this instead, and its working every time :)


Sub Activate_Custody()

Dim Data As Range
Dim RealUsedRange As Variant
Dim FirstRow        As Long
Dim LastRow         As Long
Dim FirstColumn     As Integer
Dim LastColumn      As Integer

FirstRow = "1"
FirstColumn = "1"
LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))

Set Data = RealUsedRange


ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Data


End Sub




i'm using  Range Bar chart and wish to set the "Value Label"  

to write on the bar some data which not the Y value 

setting the  IsValueShownAsLabel = true;


show me the 1st value of the pairs in AxisY

means: i have (20, 90) as Y values

Date as X value, and the value label i want to set dynamically i.e 50Kg

is this possible?

I have been trying to import 2 columns of data from separate excel named ranges into a datagridview control and have them display next to each other in separate columns.  The code below imports the first named range, and then the second.  When the code is executed both column names are present in the correct location.  However the row data below the column headers appear as follows:  The row data for the first named range appears under the first column heade as is should.  The row data for the second named range appears in the proper column under its heading but the first row of data is in the row following the last row of data in column 1.  I tried closing the connection after the first update and the reopening the second but then I get both column names and column two data but no rows from column one.  Any help would be appreciated!

        ' this code opens  writes the CofA Ingredient and Results found named ranges to the datagrid for the user to update.  
connect = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & "data source=" & BookToWorkOn & ";Extended Properties=Excel 8.0;")  
        ' Puts the ingredient column from Named Range [Ingredient} in  
adapter = New System.Data.OleDb.OleDbDataAdapter("select * From [Ingredient]", connect)  
Me.DataGridView1.DataSource = dataset.Tables(0)  
connect = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & "data source=" & BookToWorkOn & ";Extended Properties=Excel 8.0;")  
        'Adds the column data from the Named Range [Found] in  
adapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Found]", connect)  
Me.DataGridView1.DataSource = dataset.Tables(0)  


I have an address of a range and i need to determine if that range has a name.

If i use Range.Name will work for the name defined on that range, but if the name is dynamic it does not.

Is there way to get even the dynamic names when you have only the address of a range?


Steps to re-produce the behavior:

·     Add a New report item.

·     Add a Dataset with following data.

·     sers catg datp
2007 13 12
2007 14 10
2007 18 2
2007 19 3
2007 20 8
2007 21 2
2007 22 5
2007 23 4
2007 24 6
2007 25 2
2007 26 2
2007 28 3
2007 29 4
2007 30 1
2007 31 2
2007 32 2
2007 33 4
2007 35 2
2007 36 4
2007 37 2
2007 38 2
2007 39 1
2008 13 1
2008 15 1
2008 16 3
2008 17 2
2008 18 2
2008 20 1
2008 21 3
2008 28 1
2008 29 4
2008 30 2
2008 31 1
2008 32 6
2008 33 1
2008 34 1
2008 35 5
2008 37 8
2008 38 2
2008 39 2

·     Add a Chart control into the report. In the popup Select Chart Type dialog, select Line with Markers and then click OK.

·     Click the chart twice and when the Chart Data pane is displayed.

  Drop datp in data fields area.Right Click datp and select Series Properties...Change [Sum(datp)] to datp in Value field: Select [catg] in Category field:Click OKDrop catg in category fields area.Drop sers in series fields area.

·     Right click X-Axis (Catg Axis)

Specify 1 as Interval:Click OK

Preview the Report:

Please See how for 2008 series the line jumps from 13 to 18 skipping 15,16 and 17 then jumps to 34 skipping 20, 21, 28, 29, 30, 31, 32 and 33 and so on. Finally it comes back to 15 from 39.


Please advise.

Amarjot Singh

I am having trouble naming charts in Excel 2010. I am using Excel Services to publish Items in the Workbook to SharePoint. Because I have multiple charts in the workbook I do not want the names to be Chart 1, Chart 2, etc. but rather more descriptive names. The Name Manager is not enabling me to do so nor does it work to type a name into the upper left corner for named ranges. I would great appreciate any help before I pull out all of my hair!!!




I am selecting Named Ranges from differenct Excel Sheet using SQL Command. Sometime one range does not exist in a source file.

How can I check that, What is the Syntax for that?


In the above SQL Command I want to check if ETL_1 exists than select and so on



We are developing a VSTO document-level addin.

One of the requirements of the addin is to generate a large number of very long named ranges. This is done by calling a VBA macro from the VSTO addin to create the ranges.

The VSTO addin is linked to the workbook through two custom properties.

The problem is that when there are a large number of very long named ranges (9000-10000), when we save the workbook, the custom properties disappear. So when the workbook is opened again, the addin does not start.

The problem is relatively easy to reproduce

) Open a blank workbook. 
2) File -> Properties -> Custom
3) Add two properties as follows (note: I don't think it makes a difference what we call them but these are the names used for addins)
      a) _AssemblyName, Value = *
      b) _AssemblyLocation, Value = {2324342-sdfsdfdf-2323234}
4) Exit and Save workbook.
5) Open Workbook. You will probably get an error at this time about the Addin. Excel tries to use the properties created above to load a non-existent addin. Just click ok.
6) Check that properties are still there. 
7) Add the following Macro and run it

Sub CreateNamedRanges()
    Dim myName As String
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim rngCell As Range
    Set rngCell = ActiveSheet.Range("A1", "A10000")
    Dim cnt As Long
    cnt = 0
    For Each rngCell In Selection
        With rngCell
           myName = "AbcDEFxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & cnt
           wb.Names.Add Name:=myName, RefersToR1C1:= _
                "=R" & rngCell.Row & "C" & 12
        End With
        cnt = cnt + 1
    Next rngCell
End Sub

8) It takes about 30 seconds to run for 10000 ranges.
9) Once it completes, verify that the ranges are created.
10) Verify that the properties are still there - they should be.
11) Exit and Save.
12) Open the workbook again. In our case we do not receive the error message received in step 5.
13) Check the properties again. In our case the properties are gone.

We are using Windows XP SP2 with Excel 2003 Professional SP3.

We have tried to save the properties in a hidden cell when the workbook is first opened, and then rewrite them just before saving, but it seems that just after the save event and before closing, the properties are overwritten.

I don't think this is a VSTO issue as the problem seems to be reproducible with macros.

I would appreciate any ideas or help in debugging the issue.





How to get the first row index of a named range in excel sheet from vb.net code...

for example I set the column 1 of row 2 to column 5 of row 4 a range named it "MYRange" ( it is set from excel sheet )


now I want to get the starting position means 2 from my vb.net code




Excel Named Range Manager, for many versions up to 2010, provides a "Scope" setting for the user to "tag" a Named Range as either Workbook-related (Global) or Worksheet-related.

I have utilized the Object Browser in VB.Net (with no luck) to attempt to find the Syntax to Read that Scope Tag.

Any thoughts appreciated.

Below is the code I have used successfully for all aspects except for "Scope"

        XLAppMain = CType(GetObject(, "Excel.Application"), Excel.Application)
        xlsWB = CType(XLAppMain.ActiveWorkbook, Excel.Workbook)
        xlsSheet = CType(xlsWB.ActiveSheet, Excel.Worksheet)

        For Each nm In xlsWB.Names
            With xlsSheet.Cells(xlsSheet.Rows.Count, "A").End(Excel.XlDirection.xlUp).Offset(1)
                .Value = nm.Name
                .Offset(0, 1) = "'" & nm.RefersTo
                .Offset(0, 2) = nm.Name
                .Offset(0, 3) = nm.Visible.ToString
                .Offset(0, 4) = nm.Scope.ToString  <<<<< "Scope" is not supported therefore this codeline fails
            End With
        Next nm


When I delete a Name, all the formulas that use the Name become error. How can I avoid this situation. I want to delete a name using VBA but avoid errors in formulas.

The situation is complicated by the fact that a cell may contain multiple names. I want to delete all these names and give a new name with all formulas updating automatically.


While working on an excel 2003 add-in with vb.net, I need to check if a name range exists in the workbook.

Currently I have to loop through all the names and do a comparison like the following code. This becomes very slow when a workbook has over 6000 name ranges.


ForEach nm As Excel.Name In activeWorkbook.Names


  If nm.Name.equals("mysearch")Then

   do something




Next nm


I am wondering if there is a better way to do that. Something like Hashtable.ContainsKey() would be nice.




While working on an excel 2003 add-in with vb.net, I need to check if a name range exists in the workbook.

Currently I have to loop through all the names and do a comparison like the following code. This becomes very slow when a workbook has over 6000 name ranges.


ForEach nm As Excel.Name In activeWorkbook.Names



  If nm.Name.equals("mysearch")Then

   do something




Next nm


I am wondering if there is a better way to do that. Something like Hashtable.ContainsKey() would be nice. Or something like a SQL select where clause because all my names starts with "Period_"


 I also tried use a try-catch solution, assuming it exists and catch the exception if it doesn't, like the following code, it is also not ideal.




 Dim namedRange As Name = activeWorkbook.Names.Item(strName)


 Return True




 Return False


 End Try


I have an application that plots two series of time based data on a single chart. The first series is plotted as an area chart, and the second is plotted as a scatter chart with straight lines and markers. Both series' X axis is a date axis. The time intervals for the data are irregular and have different numbers of points.

Here is the issue:

Even though the axis is a date axis, and the data for each series is within the min and max of the scale, the second series will only render the up to the number of points in the first series.

So if the first series has only three points and the second has seven, the formula for the second series may include all rows, but the chart will only display the first three data points.

If the first series has 114 points and the second has 20, it renders as intended with all points of the second series displayed.

Is there a problem with the chart still treating the time scale as categories rather than ranges of time?


I'm a noob and I've already searched for the answer but couldnt find it.

I'm trying to add multiple ranges to the bar chart like Task 2 on this example. http://msdn.microsoft.com/en-us/library/dd456745.aspx

here is what I'm using to add the points


DataTable dt = new DataTable();
            SqlDataSource1.SelectParameters["param1"].DefaultValue = p1;
            SqlDataSource1.SelectParameters["param2"].DefaultValue = p2;
            DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
            dt = dv.ToTable();
            foreach (DataRow dr in dt.Rows)
                Chart1.Series["Series1"].Points.AddXY(dr["Task"].ToString(), Convert.ToDateTime(dr["StartDate"].ToString()), Convert.ToDateTime(dr["EndDate"].ToString()));


It adds a seperate task and doesn't group them.  Any help would be great.




I am working with Range Bar chart  (Gantt Chart) using SSRS 2008. I am able to display data for all the rows i got from database, but it is not showing labels for all rows that are displayed (X-Axis - here i am using task name as x-axis label) .. instead it is showing labels only for alternate rows which is very annoying.

Any help on this would be great.




This is an issue encountered in Excel Addin development.

Env: Excel2003 &2007, VS2008, C#, WPF

I define a formula in C#,  when users click enter or refresh, the formula will be calculated and return a value and write it back to the cell in excel.

a xla file defines description of the formula.

eg: formula of the cell is =GetDataPoint(A1); text of the cell is: Data Point Function (defined in xla)

If GetDataPoint(A1) goes wrong, I want to display error message in the cell, without overwriting its formula.

i.e.  range.Value2 changes from "Data Point Function" to "Error Msg",  but range.Formula stays unchanged

Discuss with other people and think this is a basic thing, should not be hard to do.

Unfortunately, I did not find an answer.

Range.Text, Range.Error are both read only.

in C#, I try to set Range.Value2 and then Range.Formula changes to be same as Value2 and vice versa.

Any advice, idea, solution?




I have data from multiple tables that I would like to display in a gridview via a stored procedure.  I need to select the data based on a date range and display it.  Once displayed I need to include a button that would allow the user to export the gridview data to an excel spreadsheet.  Does anyone have an out of the box solution or know of a tutorial I can use to accomplish this feat?  I am currently using 2.0 for this project. I am newish to .net. Thanks in advance.

Here is my stored procedure that calls the data from multiple tables.

/****** Object:  StoredProcedure [dbo].[GetUsersAll]    Script Date: 09/03/2010 08:20:23 ******/

ALTER PROCEDURE [dbo].[GetUsersAll]
	SELECT          aspnet_Users.UserName, 
FROM                     aspnet_Membership INNER JOIN
                         aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN
                         MemberInfo ON aspnet_Users.UserId = MemberInfo.memberid LEFT OUTER JOIN
                         MemberTeamRole ON aspnet_Membership.UserId =  MemberTeamRole.MemberID LEFT OUTER JOIN
                         Team ON MemberTeamRole.TeamID = Team.TeamID

				MemberInfo.firstname, MemberInfo.lastname


I created an e-mail program in VB2008.  I have an excel file with a list of all e-mail addresses and attachments to those e-mail addresses.  What I want to do is import thethe e-mail addresses and attachments into my program.  I want my program to then take each e-mail address (automatically) and insert them with their attachments into their respectable fields in the program.  The primary objective is to insert 200 e-mail addresses and attachments.  Each attachement is significant to each e-mail address.

    Example: (Dim sendTo AsNew MailAddress ("") the e-mail address would go here.

    Dim strAtt AsString

strAtt =

"" ---the attachment would go here.


Dim MsgAttach AsNew Attachment(strAtt)

Can someone guide me or help me create this code?  I would greatly appreciate it.

-Thank You



Hi Team,

I am creating a Excel file dynamically using a script, excute sql and then the dataflow.

I am not able to cretae a excel sheet name without an underscore

exisitng file: "team data" even though am giving the table name in excute sql task(which is excel sheet name) as "team data" it is creating the name as "team_data" because of which the package is failing?

I am able to achieve this with an underscore, as it is a client requirement to generate wihtout an underscore am posting this question.

Is this an issue with Sql server 2005?




I have project where I am create Linq 2 Sql files for each group of business objects.  For this case these groups are People and Users.  There is a relationship of a User to Person but a person can stand alone.  The issue is that both of these Linq files has a reference to table named dbo.People.  When I try to run this project in a Dynamic Data project I get the following error.

The name for entity 'openPlat.Models.PeopleTables.People' in context 'openPlat.Models.PeopleTables.PeopleDataContext' conflicts with the name for the entity 'openPlat.Models.UserTables.People' in context 'openPlat.Models.UserTables.UsersDataContext'.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: The name for entity 'openPlat.Models.PeopleTables.People' in context 'openPlat.Models.PeopleTables.PeopleDataContext' conflicts with the name for the entity 'openPlat.Models.UserTables.People' in context 'openPlat.Models.UserTables.UsersDataContext'.

Source Error:

Line 28:             // class in your application.
Line 29:             model.RegisterContext(typeof(openPlat.Models.UserTables.UsersDataContext), new ContextConfiguration() { ScaffoldAllTables = true });
Line 30:             model.RegisterContext(typeof(openPlat.Models.PeopleTables.PeopleDataContext), new ContextConfiguration() { ScaffoldAllTables = true });
Line 31: 
Line 32:             // The following statement supports separate-page mode, where the List, Detail, Insert, and 

Source File: H:\Users\Owner\Documents\Visual Studio 2008\Projects\openPlat\openPlat\Global.asax.cs    Line: 30

Stack Trace:

[ArgumentException: The name for entity 'openPlat.Models.PeopleTables.People' in context 'openPlat.Models.PeopleTables.PeopleDataContext' conflicts with the name for the entity 'openPlat.Models.UserTables.People' in context 'openPlat.Models.UserTables.UsersDataContext'.]
   System.Web.DynamicData.MetaModel.CheckTableNameConflict(MetaTable table, String nameOverride, List`1 tablesToInitialize) +159679
   System.Web.DynamicData.MetaModel.RegisterContext(DataModelProvider dataModelProvider, ContextConfiguration configuration) +758
   System.Web.DynamicData.MetaModel.RegisterContext(Func`1 contextFactory, ContextConfiguration configuration) +359
   System.Web.DynamicData.MetaModel.RegisterContext(Type contextType, ContextConfiguration configuration) +79
   openPlat.Global.RegisterRoutes(RouteCollection routes) in H:\Users\Owner\Documents\Visual Studio 2008\Projects\openPlat\openPlat\Global.asax.cs:30
   openPlat.Global.Application_Start(Object sender, EventArgs e) in H:\Users\Owner\Documents\Visual Studio 2008\Projects\openPlat\openPlat\Global.asax.cs:59

[InvalidOperationException: An error occurred during model registration and this operation cannot be performed.]
   System.Web.DynamicData.MetaModel.CheckForRegistrationException() +160119
   System.Web.DynamicData.MetaModel.get_Default() +4
   openPlat._Default.Page_Load(Object sender, EventArgs e) in H:\Users\Owner\Documents\Visual Studio 2008\Projects\openPlat\openPlat\Default.aspx.cs:21
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

Any advice on how to handle using a table in multiple Linq 2 Sql files would be greatly appreciated.

<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure