Create charts using dynamic ranges

0
votes

I'm having some problems using VBA in Excel to create charts using dynamic ranges. What I need to do is chart the results of each of the compounds for each sample. Both the number of samples and the number of compounds can vary. An example of the datasheet can be found here.

A sample of the code I tried to write, however my range has not been properly defined:

Sub Graph()

Dim r As Range
Dim c As Range
Dim wks As Worksheet

Set wks = ActiveSheet

'
' Graph Macro
'
Set r = Range("B2").End(xlDown)
Set c = Range("D2").End(xlToRight)

ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("wks!$B$2:B" & r, "wks!$P$2:P" & c)
'

End Sub

Could anyone point me in the right direction?

1
excelvbacharts

1 Answers

0
votes

Try this sub instead:

Sub Graph()
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    With [a1].CurrentRegion
        ActiveChart.SetSourceData Source:=Range(.Resize(, 1).Offset(, 1), .Offset(, 3).Resize(, .Columns.Count - 3))
    End With
End Sub