none
Excel Chart - Dynamic named range skips data

    Question

  • Hello,

     

    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()

    Sheets("Custody").Unprotect
    Sheets("Custody").Activate

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

    Sheets("Custody").Protect

    End Sub

     

    The named range CustodyData is set using the following formula;

     

    =OFFSET(qtabHistoricProceedingsCustodyB!$J$1,0,0,COUNTA(qtabHistoricProceedingsCustodyB!$J:$J),6)

    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.

    Tuesday, November 02, 2010 11:36 AM

Answers

  • 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
        
    Sheets("qtabHistoricProceedingsCustodyB").Activate

    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

    Sheets("Custody").Unprotect
    Sheets("Custody").Activate

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

    Sheets("Custody").Protect

    End Sub

    • Marked as answer by Bessie Zhao Tuesday, November 09, 2010 8:59 AM
    Thursday, November 04, 2010 9:59 AM

All replies

  • 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"


    Cheers,

    Andy
    www.andypope.info

    Tuesday, November 02, 2010 12:36 PM
  • 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?

    Tuesday, November 02, 2010 1:01 PM
  • 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?

     


    Cheers,

    Andy
    www.andypope.info

    Tuesday, November 02, 2010 1:14 PM
  • 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,

    Eric

     

    Tuesday, November 02, 2010 2:12 PM
  • 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.

    Tuesday, November 02, 2010 2:42 PM
  • 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.

     


    Cheers,

    Andy
    www.andypope.info

    Tuesday, November 02, 2010 2:49 PM
  • 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
        
    Sheets("qtabHistoricProceedingsCustodyB").Activate

    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

    Sheets("Custody").Unprotect
    Sheets("Custody").Activate

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

    Sheets("Custody").Protect

    End Sub

    • Marked as answer by Bessie Zhao Tuesday, November 09, 2010 8:59 AM
    Thursday, November 04, 2010 9:59 AM