2

I have an table and would like to generate an chart out of it. the problem is I would like to have only the column Values of A, F, G, and H.

I tried seriescollection(1).deletion method,. but I am still getting the column B and C series in my chart.

Could anyone tell me how I can do it. Any lead would be helpful 

Sub chartstatus()
Dim Rng As Range
Dim cht As Object

Set Rng = ActiveSheet.Range("A2:H53")
ThisWorkbook.Sheets("Status").ChartObjects.delete
Set Sh = ActiveSheet.ChartObjects.Add(Left:=650, _
    Width:=600, _
    Top:=80, _
    Height:=250)

Sh.Select

Set cht = ActiveChart
With cht
.SetSourceData Source:=Rng
.ChartType = xlColumnClustered
cht.Axes(xlSecondary).TickLabels.NumberFormat = "0.%"

End With


cht.SeriesCollection(6).Name = " % Missing "
cht.SeriesCollection(7).Name = " % OnTime"
cht.SeriesCollection(8).Name = " %  Delayed"


cht.SeriesCollection(6).HasDataLabels = True
cht.SeriesCollection(7).HasDataLabels = True
cht.SeriesCollection(8).HasDataLabels = True
cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 255, 255) '<~~ Red

cht.HasTitle = True
cht.ChartTitle.Text = "Result "
End Sub
Jenny
  • 441
  • 2
  • 7
  • 19
  • 1
    I helped you here https://stackoverflow.com/questions/45345406/formatting-with-charts , but for some reason you are using your old code again – Shai Rado Jul 28 '17 at 10:35
  • @ShaiRado please don't mistake, the question now was different. I asked how I can delete the series. – Jenny Jul 28 '17 at 10:43
  • also, I felt I should not use the code of the expert and ask help from that. If I dint abide the rules of forum, forgive me. It wont be repeated again – Jenny Jul 28 '17 at 10:50
  • It's ok, there aren't such strict rules :) The code you get from users that assist you, is yours, so you can also learn how to have a better use of VBA. – Shai Rado Jul 28 '17 at 10:52
  • @ShaiRado the issue is , I just would like to have the Column A values for my axis and I don't need them to be displayed in my chart. With the condition now, the chart assumes my Column A value as the highest. BEcause of this reason, I am not able to see the values of E and F properly. They are percentage values while, the column A is an absolute number – Jenny Jul 28 '17 at 11:21
  • 1
    you used a ´with cht´ , and then you ended it, only to type each line cht again, why? You even used a cht inside the with. – Patrick Lepelletier Jul 28 '17 at 23:04

1 Answers1

3

You can combine 2 range into 1 range

Dim Rng As Range
Dim Rng_A As Range
Dim Rng_FH As Range
Set Rng_A = ActiveSheet.Range("A2:A52")
Set Rng_FH = ActiveSheet.Range("F2:H52")
Set Rng = Union(Rng_A, Rng_FH)
kevin
  • 187
  • 15