Sergei,

This is quite possible. Are you row numbers going to be hard coded, or is there some criteria you choose to know to use rows 13,18,etc?

Here is an example using a specified (non-adjacent) range, it uses formula array notation to populate the series and title:

Sub SergeiExample()

Dim RG As Range, CLL As Range

Dim sValues As String, sTitles As String

Set RG = Range("D13,D18,D27,D28,D33,D50")

For Each CLL In RG.Cells

If Len(sTitles) = 0 Then

sTitles = "={""" & CLL.Text & """"

sValues = "={" & CLL.Offset(0, 4).Value

Else

sTitles = sTitles & ",""" & CLL.Text & """"

sValues = sValues & "," & CLL.Offset(0, 4).Value

End If

Next

If Len(sTitles) > 0 Then

sTitles = sTitles & "}"

sValues = sValues & "}"

Charts.Add

ActiveChart.ChartType = xlColumnClustered '*** change as needed

ActiveChart.SeriesCollection(1).XValues = sTitles

ActiveChart.SeriesCollection(1).Values = sValues

ActiveChart.SeriesCollection(1).Name = "=""Series Title"""

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart sheet name"

End If

End Sub

The Offset statement gets the value, as column H is 4 columns to the right of D. If you need any help with determining the range to use as source data, please just let me know how you decide which to use and we can incorporate that into the code.

Matt

This is quite possible. Are you row numbers going to be hard coded, or is there some criteria you choose to know to use rows 13,18,etc?

Here is an example using a specified (non-adjacent) range, it uses formula array notation to populate the series and title:

Sub SergeiExample()

Dim RG As Range, CLL As Range

Dim sValues As String, sTitles As String

Set RG = Range("D13,D18,D27,D28,D33

For Each CLL In RG.Cells

If Len(sTitles) = 0 Then

sTitles = "={""" & CLL.Text & """"

sValues = "={" & CLL.Offset(0, 4).Value

Else

sTitles = sTitles & ",""" & CLL.Text & """"

sValues = sValues & "," & CLL.Offset(0, 4).Value

End If

Next

If Len(sTitles) > 0 Then

sTitles = sTitles & "}"

sValues = sValues & "}"

Charts.Add

ActiveChart.ChartType = xlColumnClustered '*** change as needed

ActiveChart.SeriesCollecti

ActiveChart.SeriesCollecti

ActiveChart.SeriesCollecti

ActiveChart.Location Where:=xlLocationAsNewShee

End If

End Sub

The Offset statement gets the value, as column H is 4 columns to the right of D. If you need any help with determining the range to use as source data, please just let me know how you decide which to use and we can incorporate that into the code.

Matt