Issue with Union

This is a working code. The graph can be updated based on this

With ActiveChart
    .SetSourceData Source:=CurrentSheet.Range("B5:B7,F5:F7,B16,F16") 'this works!!
End With

However, my the final cell might change.Hence I use 'Projects' to determine the last filled Cell. To create a range, I use the Union commmand.

Projects = CurrentSheet.Range("B16").End(xlUp).Row
Set myrange = Union(CurrentSheet.Range(Cells(5, 2), Cells(Projects, 2)), CurrentSheet.Range(Cells(5, 4), Cells(Projects, 4)), CurrentSheet.Range("B16"), CurrentSheet.Range("D16"))
With ActiveChart
    .SetSourceData Source:=Range("myrange")
End With

But when i run this routine, it shows
Error message
Run-time error '1004':
Method 'Range' of object'_Worksheet' failed.

This points to this sentence of my quote

   .SetSourceData Source:=Range("myrange")

anyone have any idea what went wrong????

Hello jieyi,

You have set the object variable "myrange" to the ranges in the Union and that's correct. The problem lies in how you are assigning the range to the chart. You are trying to reference your new range as a named ranged, which it isn't. See below in red...

Projects = CurrentSheet.Range("B16").End(xlUp).Row
Set myrange = Union(CurrentSheet.Range(Cells(5, 2), Cells(Projects, 2)), CurrentSheet.Range(Cells(5, 4), Cells(Projects, 4)), CurrentSheet.Range("B16"), CurrentSheet.Range("D16"))
With ActiveChart
    .SetSourceData Source:=Range("myrange")
End With

The code should be as below...

Projects = CurrentSheet.Range("B16").End(xlUp).Row
Set myrange = Union(CurrentSheet.Range(Cells(5, 2), Cells(Projects, 2)), CurrentSheet.Range(Cells(5, 4), Cells(Projects, 4)), CurrentSheet.Range("B16"), CurrentSheet.Range("D16"))
With ActiveChart
    .SetSourceData Source:=myrange
End With