I would like to ask if it is possible to add a non continuous range as a data source to create a sparkline. Without VBA it can be done by creating a named range, provided that data are on the same worksheet and using that named range as a data source.

My named range is called "SPA". I can even record a macro, but when I run the recorded macro on the correct activesheet I get Application-defined or object-defined error.

This is the code that was generated when recording the macro Range("$B$2").SparklineGroups.Add Type:=xlSparkColumn, SourceData:="SPA"

Here is another code, which I actually use in my macro, where all the ranges are fully qualified SM.Range("A2").SparklineGroups.Add Type:=xlSparkColumn, SourceData:=SM.Range("SPA")

In both cases I get Application-defined or object-defined error. I have a custom to rename Sheet1, Sheet2, etc. codenames to own abbreviations, so that I do not need to qualify sheets in every macro. So I use SM.Range("A2") instead of Dim SM as Worksheet: Set SM = ThisWorkbook.Sheets("SM") so the error does not relate to the ranges not being fully qualified.

When I use add sparkline manually without VBA and I use the named range, the non continuous data source range works.

Thank you very much for any hints.