In Excel VBA I need to plot a scatter plot with dynamic ranges

Question

I have tables of test results imported from text files. These tables vary in the number of rows and the starting row. I have done the routines for finding the start and finish rows.

In the following code things are fine if start and finish rows are 12 and 211. How do I feed different indices into the setsourcedata routine? I want to plot X versus Y1 and Y2 on the same chart. Thanks for any help

With Charts.Add.Location(Where:=xlLocationAsObject, Name:=SheetName)

        .ChartType = xlXYScatterSmoothNoMarkers

        .SetSourceData Sheets("sheet1").Range("A12:A211,C12:C211,AD12:AD211"), xlColumns

        .HasTitle = True
        .ChartTitle.Font.Size = 10
        .ChartTitle.Text = "Test Date " & SheetName
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Passes"
        .Axes(xlCategory, xlPrimary).MinimumScale = 0
        .Axes(xlCategory, xlPrimary).MaximumScale = 10000
        .Axes(xlCategory, xlPrimary).HasMajorGridlines = True
        .Axes(xlCategory).Crosses = xlMinimum

        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Rut depth (mm)"
        .Axes(xlValue, xlPrimary).MinimumScale = -15
        .Axes(xlValue, xlPrimary).MaximumScale = 0
        .Axes(xlValue, xlPrimary).Crosses = xlMinimum
        'The Parent property is used to set properties of the Chart.

        With .Parent
              .Name = "MyDataChart"
              .Top = .Parent.Range("F2").Top
              .Left = .Parent.Range("E2").Left
              .Width = 350
              .Height = 200
        End With
  End With

Answer 1

Assuming your columns of data are continuous (don't have any intermittent blanks) you can try something like:

Dim col1 as Range, col2 as Range, col3 as Range

Set col1 = ActiveSheet.Range("A12",Range("A12").End(xlDown))
Set col2 = col1.Offset(0,2)  'Return column C, same # of rows 
Set col3 = col1.Offset(0,3)  'Return column D, same # of rows 

.SetSourceData Sheets("sheet1").Range(col1.Address & "," & col2.Address & "," & col3.Address)