none
Using Visual Basic to create charts with Dynamic Ranges RRS feed

  • Question

  • I have a worksheet that has data where every two columns is that data for a specific category (i.e., Column A & B, would be Cateogry 1, etc.), and the number of rows in a category will vary.  I need to create Pie Charts for each category.  Everything works find until I run across a category that only has 1 row of data.  I thought I had a solution that would work (see code below), but it still charts multiple rows (not every single row like it was previously) but not just the one.  It is probably something simple, but I am still new to VB and have now spent half the day trying to find what I'm doing wrong.  Here's the Code I'm trying to use, I have a routine created for each category, so once I find the solution for this one, I will be able to apply it across all of the categories.  I just need to get this one working...

      

        Dim myR As Long
        myR = Cells(Rows.Count, 2).End(xlUp).Row
       
        Range("A3:B3" & myR).Select
       
        Charts.Add

    Thank you so much for you assistance.  A very frustrated flower. :)

     

    FYI - I did review the links it suggested for questions similar to mine, but it did not help.


    Lisa
    Monday, December 5, 2011 11:45 PM

Answers

  • Dim myR As Long
    myR = Cells(Rows.Count, 2).End(xlUp).Row
    Range("A3:B" & myR).Select
    Charts.Add

    should work, as long as the sheet with the data is active - it worked for all cases in my testing.

    Sorry for the delay - the forums have been acting up today.


    HTH, Bernie
    Thursday, December 8, 2011 1:43 AM
  • Great - can you mark my reply as the answer so that everybody knows not to worry about this thread anymore?

    And Merry Christmas to you, too.


    Bernie
    Thursday, December 8, 2011 5:15 PM

All replies

  • Try changing

    Range("A3:B3" & myR).Select

    to

    Range("A3:B" & myR).Select

     

    BUT -  what you really should do is not select, but use objects to make looping easier: this will add a chart for every two columns of data on worksheet "Sheet1"

    Sub test()
    Dim myChart As Chart
    Dim i As Integer
    Dim lastCol As Integer

    With Worksheets("Sheet1") 'Change to worksheet with data
    lastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column - 1
    For i = 1 To lastCol Step 2
    Set myChart = Charts.Add
    myChart.SetSourceData .Range(.Cells(3, i), .Cells(.Rows.Count, i + 1).End(xlUp))
    Next i
    End With
       
    End Sub

     


    HTH, Bernie

    Tuesday, December 6, 2011 2:19 AM
  • Bernie,

    THANK YOU.

    I'm sorry, I probably should have provided a little more info on how this workbook is used.  This workbook is a tool to be used by my team to create multiple charts and graphs for Category Analysis by either a button that does each indivdual category or a button for common groups of categories depending on the team members need at the time.

    I use code that adjusts the Pivot Table accorindingly to filter the data to the needed category and copys and pastes that into a separate worksheet (BP Data)in the workbook.  I use the data pasted to BP Data to create the Pie Chart.  The code you've suggested works, but because the category or categories each individual team member will be analyzing will vary from member to member and from one time to another, I don't need it to loop. 

    I tried Range("A3:B" & myR).Select but it works if there is one row in the data set, but not if there is multiple rows in the data set

    I also tried the other code you recommended. This was helpful and gets me closer to my end need, but I don't need it to loop.  I need it to just to chart one data set, keeping in mind that on some occasions this data set could be one row, or mutliple rows to chart and it is possible for the columns on either side of the 2 coulmsn in the data set being charted could be blank; however, the data set for a category will always be in the same to columns. 

    I've tried altering the code you recommended, trying with my limited experience to adjust it to do what I need, but I get close, but not quite what I need.

    Thank you for your assistance.  This is one of my favorite sites to look for help with Visual Basic and Macro questions.

     

    Tuesday, December 6, 2011 1:25 PM
  • Dim myR As Long
    myR = Cells(Rows.Count, 2).End(xlUp).Row
    Range("A3:B" & myR).Select
    Charts.Add

    should work, as long as the sheet with the data is active - it worked for all cases in my testing.

    Sorry for the delay - the forums have been acting up today.


    HTH, Bernie
    Thursday, December 8, 2011 1:43 AM
  • Well, it worked now...not sure what's different today than the other day, when I tried at least 20 times.  Thank you so much for your help.  No worries about the delay...I'm just glad someone responded.  Have a Merry Christmas.
    Thursday, December 8, 2011 4:32 PM
  • Great - can you mark my reply as the answer so that everybody knows not to worry about this thread anymore?

    And Merry Christmas to you, too.


    Bernie
    Thursday, December 8, 2011 5:15 PM