change legend of chart with multiple series

I believe this modified snippet will work for you. Substitute the names you want to use for the myName entries.

With ActiveSheet.Shapes.AddChart.Chart
 .SetSourceData Source:=Union(RNG1, RNG2, RNG3)
 .ChartType = xlColumnClustered
 .SeriesCollection(2).ChartType = xlLine
 .SeriesCollection(1).Name = "myName1"
 .SeriesCollection(2).Name = "myName2"
 .SeriesCollection(3).Name = "myName3"
 .PlotBy = xlRows
End With