0

I have some data tabulated onto worksheet("MAIN") columns Y:AB. The data were to be plotted as a chart. I would like to make the start and the end of data dynamically change with the start of row (Range("E8"), calculated) and the end of the row (Range("E9"), calculated).

I have the code:

Sub chRNG()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MAIN")

Dim st As Long, ed As Long
st = ws.Range("E8").Value       'start of data row, obtained from worksheet formula
ed = ws.Range("E9").Value       'end of data row, obtained from worksheet formula

Dim Field1, Field2, Field3, Field4, Field5 as long
Field1 = ws.Range("A:AZ").find("criteria_1").column
'
'
'
Field5 = ws.Range("A:AZ").find("criteria_5").column

Dim chRng As Range
Set chRng = ws.Range(Cells(st, Field1), Cells(ed, Field5))

With ws
.ChartObjects(1).Chart.SetSourceData _
    Source:=.Range("chRng"), _
    PlotBy:=xlColumns
End With

End Sub

Every-time I run this procedures, only the end of data was updated, but not the start of data. When I right-click on the chart, choose "select data" and examine the data source, the source became =MAIN!$X$1:$AB$4401 let's say.

Why the start wasn't updated?

braX
  • 11,506
  • 5
  • 20
  • 33
John Liu
  • 37
  • 5
  • Probably a side note, but see [this](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet): `Set chRng = ws.Range(Cells(st, Field1), Cells(ed, Field5))` is problematic. – BigBen Jun 02 '20 at 20:52
  • 1
    `Source:=.Range("chRng")` - should be `Source:=chRng` probably. – BigBen Jun 02 '20 at 20:52
  • Did something similar a long time ago, but the chart referenced a fixed set of cells and the data in those cells was updated as needed. – Solar Mike Jun 02 '20 at 21:04
  • @BigBen thank you. You're right with the syntax. My chart, however, have date series also. My code cannot update the date range. Any ideas? – John Liu Jun 02 '20 at 21:06

0 Answers0