Charting values continuously (not as points) over varying distances

[Be a Pro]

First of all, "Hello all" and thank you for considering my dilemma. It's simply beyond my current skill.

I'm making charts of separate data sets all with data values between 1 and 5 assigned to varying distances. I have to make charts in illustrator for better quality (PhD standards), but it would be a lot simpler to see an excel chart and then copy what I'm seeing rather than reading every single number.

I'm trying to make this:
Chart Example

Out of this:
Spreadsheet Example

The problem is that I need excel to maintain the value between distance ranges, but it is only plotting the beginning point (xy scatter). When there is no value there, however, it needs to be clear that there is nothing there. The "no value" gaps in the data are as important as the values. I would like it to show up in a 1000ft box displaying whatever values are there so that I could just copy and paste 1000ft sections of data into the chart data range and have it display instantly. If I could do that, it would be FAN-TASTIC.

Thoughts?

 

[Cindy Ellis]

I've got 40,000 feet with about 8-10 data sets for each foot to finish

If the data all follows the same general data organization, it would be worth it to automate it in a way that doesn't affect the layout of the original data at all.

I put together a little routine that makes a copy of the active sheet and rearranges the data to be amenable to creating the charts as in the previous post. It doesn't include creating the charts, but by copying the depth column to the right of the signal data, making the chart is really quite easy. If this seems like a practical way to go, I'll add in the chart-creation parts.

Sub DataForCharts()
'
'
    BaseSheet = ActiveSheet.Name
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = BaseSheet & " for charts"   'new sheet is now active
    LastDataRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row  'Column C is the column with data
    LastDataColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column 'use row 2 to get last column
    For i = LastDataRow To 3 Step -1
        Rows(i).Copy
        Rows(i).Insert Shift:=xlDown, CopyOrigin:=Rows(i)
        Application.CutCopyMode = False
    Next i
    Range("C3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C3").Value = Range("B3").Value
    Columns("C:C").Copy Destination:=Columns(LastDataColumn + 2)
    
End Sub