Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Creating multiple indivdual charts vs. one chart with multiple series

Very new to VBA so please be gentle.  I have some some dynamic data varying in row and column length.  The labels are in column A with the data spaning several columns.  I'm trying to create a singe run chart for each column of data.  My first crack at the code gives me the charts, but the 1st has one series of data, the 2nd has two series, etc.  

How do I change the code to limit the chart to just the data on that column?  

Thanks
Sub My_chart()
    Dim MyRng As Range
    Dim MyDte As Range
    Dim arow As Integer
    Dim acol As Integer
    Range("A1").Activate
    
    arow = Range("A1").SpecialCells(xlLastCell).Row
    acol = Range("A1").SpecialCells(xlLastCell).Column
    
    Set MyDte = Range("B1:B" & arow)
    Set MyRng = Range("$A$1:$A$" & arow, MyDte)
    
    Do
     MyRng.Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.SetSourceData Source:=MyRng
        ActiveChart.ChartType = xlLine
        MyDte.Offset(0, 1).Select
        Set MyDte = MyDte.Offset(0, 1)
        Set MyRng = Range("$A$1:$A$" & arow, MyDte)
    Loop While Not IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Open in new window

0
WhackyAlaskan
Asked:
WhackyAlaskan
  • 2
1 Solution
 
SiddharthRoutCommented:
Try this

Sub My_chart()
    Dim MyRng As Range
    Dim MyDte As Range
    Dim arow As Integer
    Dim acol As Integer
    Range("A1").Activate
    
    arow = Range("A1").SpecialCells(xlLastCell).Row
    acol = Range("A1").SpecialCells(xlLastCell).Column
    
    Set MyDte = Range("B1:B" & arow)
    Set MyRng = Range("$A$1:$A$" & arow, MyDte)
    
    Do
     MyRng.Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.SetSourceData Source:=MyRng
        ActiveChart.ChartType = xlLine
        MyDte.Offset(0, 1).Select
        Set MyDte = MyDte.Offset(0, 1)
        Set MyRng = Union(Range("$A$1:$A$" & arow), MyDte)
    Loop While Not IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Open in new window


Sid
0
 
WhackyAlaskanAuthor Commented:
That did it. I found that it was missing the last column of data so set
Loop While Not IsEmpty(ActiveCell.Offset(0, 0)) and it works perfectly.  

Thanks!
0
 
WhackyAlaskanAuthor Commented:
Thanks for the rapid response!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now