Creating Line Chart for each Row using VBA excel (Dynamic Row,Column)

Question

I would appreciate if someone can help me with this code

-- I have dynamic rows and columns, the code finds number of rows and columns using LastRow, and LastColumn. I have to plot line chart for each row (keeping the coulmn fixed at the number found) and place it in sheet 2. I created a hybrid code with recording and looping (As i am a new to coding). The excel sheet table i have to plot is given below (and it can be dynamic both in rows and columns. Cell, Counter etc are header, First row is A ,Nbr, etc ). Please Help

Cell Counter 0:45 1:00 1:15 1:30 1:45 2:00 2:15 2:30
A Nbr 10 54 45 0 0 0 0 0

 Dim i As Long        
 Dim LastRow As Long        
 Dim LastColumn As Long        
 Dim cht As Chart          

  LastRow = Range("A65536").End(xlUp).row         
  LastColumn = Range("A1").End(xlToRight).Column     

For i = 2 To LastRow               
        Dim location As String              

Range("$A$i:$LastColumn").Select    
    ActiveSheet.Shapes.AddChart.Select           
    ActiveChart.ChartType = xlLine      
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$i:$LastColumn")          

  With ActiveChart.Parent     
     .Height = 225 ' resize     
     .Width = 500  ' resize     

      ActiveChart.ChartArea.Copy     
Sheets("Sheet2").Select     
ActiveSheet.Pictures.Paste.Select         
Sheets("Sheet1").Select     
Application.Run ("DeleteEmbeddedCharts")     

  End With     
 Next i                
 End Sub

Santosh · Accepted Answer · 2013-11-15 11:06:18Z

3

Try below code

Sub main()
   'variable declaration
    Dim i As Long
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim chrt As Chart

    'Find the last used row
    LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row

    'Find the last used column
    LastColumn = Sheets("Sheet1").Range("A1").End(xlToRight).Column

    'Looping from second row till last row which has the data
    For i = 2 To LastRow
        'Sheet 2 is selected bcoz charts will be inserted here
        Sheets("Sheet2").Select

        'Adds chart to the sheet
        Set chrt = Sheets("Sheet2").Shapes.AddChart.Chart
        'sets the chart type
        chrt.ChartType = xlLine

        'now the line chart is added...setting its data source here
        With Sheets("Sheet1")
            chrt.SetSourceData Source:=.Range(.Cells(i, 1), .Cells(i, LastColumn))
        End With

        'Left & top are used to adjust the position of chart on sheet
        chrt.ChartArea.Left = 1
        chrt.ChartArea.Top = (i - 2) * chrt.ChartArea.Height

        Next

End Sub

enter image description here

edited Nov 15, 2013 at 11:06

answered Nov 5, 2013 at 0:36

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nadeem Over a year ago

Hi Santosh, Firstly Thanks for your help in answering my question. The code you gave me gives single line chart for all the rows. I wanted to plot one line Chart for each row. For example if i have 8 rows i shld get one chart for each row (i.e 8 charts). Keeping Columns always constant

Nadeem Over a year ago

Thanks A lot santosh for all your help. Really what i was looking for.

Nadeem Over a year ago

@ Santosh, One more help if possible. The plots on both sheet1 and sheet2 are getting stacked one over other. Do you know a way pasting them one below the other in sheet2?

Nadeem Over a year ago

@ Santosh, You are a star mate. Thanx for the help. It works perfectly fine. If it is not too much and based on your free time, can you please add comments

Santosh Over a year ago

@Nadeem Thanks for the kind words of appreciation. I have added the comments but let me know if you have questions. Don't forget to accept the answer. How to accept answer

|

Collectives™ on Stack Overflow

Creating Line Chart for each Row using VBA excel (Dynamic Row,Column)

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related