How to generate graph without taking of time using vba?

Question

Hello I have written a code for generating the graph and it is working correctly. The problem is it is taking lot of time to generate. and i am not getting why it is taking time. the code is

    Dim cc As Chart

    Set cc = ActiveWorkbook.Charts.Add
    Set cc = cc.Location(Where:=xlLocationAsObject, name:=assume)

    With cc

     .ChartType = xlXYScatterLines

     With .Parent

       .Top = Columns(b).Offset(0, 4).Top
       .Left = Columns(b).Offset(0, 4).Left
       .name = "cc"

     End With

  End With

  Dim sc As Series
  Set sc = cc.SeriesCollection(1)

  With sc
      .Values = Columns(b).Offset(0, -3)
      .XValues = Columns(b).Offset(0, -5)
  End With

Please somebody help me

well, it's hard to say why it's taking so much time, but I do see that you are using an 2 entire columns worth of data as your source. That could potentially be a big ask of Excel. Are you sure this is what you are after? — Scott Holtzman
– Scott Holtzman, Commented Oct 17, 2012 at 14:44
Hello Scott May be you are right But may i know how to select a particular data. I mean i hav data in colum "A" and i dont no how much it has so, it should select entire data — M_S_SAJJAN
– M_S_SAJJAN, Commented Oct 19, 2012 at 8:16
Well, then you should see dynamic named ranges. Or you can dynamically creating your range in VBA, so that you only get the rows in the column that have data. I can show you how very quickly, if you wish. — Scott Holtzman
– Scott Holtzman, Commented Oct 19, 2012 at 13:30

Scott Holtzman · Accepted Answer · 2012-10-22 13:55:43Z

1

Try this, to just chart the rows of the column with actual data.

Sub makeChart(b As String, assume As String) 'i presupposed these arguments based on your code

Application.ScreenUpdating = False

Dim cc As Chart

Set cc = ActiveWorkbook.Charts.Add
Set cc = cc.Location(Where:=xlLocationAsObject, Name:=assume)

With cc

    .ChartType = xlXYScatterLines

    With .Parent

        .Top = Columns(b).Offset(0, 4).Top
        .Left = Columns(b).Offset(0, 4).Left
        .Name = "cc"

    End With

End With


Dim strValue As String, strXValue As String

'here you are using the passed column letter to find the specific column you want to chart
strValue = Split(Range(b & "1").Offset(, -3).Address, "$")(1) 'will return "C" if given column F
strXValue = Split(Range(b & "1").Offset(, -5).Address, "$")(1) 'will return "A" if given column F

Dim sc As Series
Set sc = cc.SeriesCollection(1)

With sc

    'will select from row 1 to the last true used row in the given column
    .Values = Range(strValue & "1:" & strValue & Range(strValue & Rows.Count).End(xlUp).Row)
    .XValues = Range(strXValue & "1:" & strXValue & Range(strXValue & Rows.Count).End(xlUp).Row)

End With

Application.ScreenUpdating = True

End Sub

answered Oct 22, 2012 at 13:55

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

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

2 Comments

M_S_SAJJAN Over a year ago

Thank you very much for above code it works exactly how i want but can you tell me how to add more series to same graph with same syntax for Xvalue and Values

Scott Holtzman Over a year ago

@M_S_SAJJAN -> if you have an additional question, please post a new question to SO.

Air_Cooled_Nut · Accepted Answer · 2012-10-17 16:49:31Z

1

Have you turned off screen updating? Add this to the beginning of your code:

Application.ScreenUpdating = False

Then add this at the very end of your code:

Application.ScreenUpdating = True

answered Oct 17, 2012 at 16:49

Air_Cooled_Nut

1373 gold badges5 silver badges22 bronze badges

1 Comment

M_S_SAJJAN Over a year ago

Hello I used above code It works good but its taking still too much time as Scott explained it may b because of selecting entire coulmn so itried to select only data in column that is With sc .Values = Columns(b).End(xlDown)..Offset(0, -3) .XValues = Columns(b).End(xlDown).Offset(0, -5) End With But it is giving error PLEASE HELP ME

Collectives™ on Stack Overflow

How to generate graph without taking of time using vba?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related