how to add multiple rows in excel into a chart

Axes(xlCategory).TickLabels.NumberFormat = '#,#0' '.Axes(xlCategory).TickLabels.NumberFormat = '#,#000' Cells(9, u).Addressĭebug.Print 'Value Range: ' & CountsRng.Address Cells(LastRow, 3))ĭebug.Print 'Name Range: ' &. Here is the complete and working code for anyone interested: Sub MakeChart()ĭebug.Print ('Last Column: ' & LastColumn & ' Count: ' & ColumnCount & ' LastRow: ' & LastRow) A better way was to use Set ChartObj = (Left:=20, Width:=800, Top:=20, Height:=500)Īs this will create a completely empty graph to which you can add your own series Cells(LastRow, 3))Īlso, the use of Charts.Add didnt help very much as Excel tries to automatically find the correct ranges for all series and adds them resulting in a completely messed up chart. You cannot use Set xRng = Sheets('Data').Range('R14:C3', 'R' & LastRow & ':C3')īut rather have to use Set xRng =. It seems as I have somehow completely messed up the notation of the cell range. ' Debug.Print ('CountsRng: R14:C' & u & ', R' & LastRow & ':C' & u & ' NameRng: ' & NameRng & ' xRng: R14:C3, R' & LastRow & ':C3')ĪctiveChart.SeriesCollection(i).XValues = xRngĪctiveChart.SeriesCollection(i).Values = CountsRngĪctiveChart.SeriesCollection(i).Name = NameRng Set CountsRng = Sheets('Data').Range('R14:C' & u, 'R' & LastRow & ':C' & u) NameRng = Sheets('Data').Range('R9:C' & u).Value ' Debug.Print ('Last Column: ' & LastColumn & ' Count: ' & ColumnCount & ' LastRow: ' & LastRow) LastRow = ActiveSheet.Range('C' & ).End(xlUp).Row

LastColumn = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column The Object Catalogue was a bit of a help, however my problem persists. I already read various sources and tried many scripts, none seem to work. I'm not an expert in VBA so please be kind. My big problem is, that I can't find a way to dynamically add all the data columns as series to my chart together with the respective name. The sheet ActiveSheet (or Sheets('Data')) has Rows from C14 until Cend containing the XValues and Columns from E14:Eend until R14:Rend where 'end' marks the last row of data as determined by column C.

What I've come up with but doesn't work is the following: I don't know beforehand how many series there are so it needs to be dynamic. I'm trying to dynamically add multiple series to a line chart.

How to add multiple rows in excel into a chart