Duplicate value from bottom of one column to top of another

[abunn3]

I've got a series of XYZ position data that I've binned into 5 ranges so that I can plot each range in a different color in an XY scatter plot (only plotting 2 points of the position at a time). I would like to use smooth lines to connect these data points, but when switching from range to range a gap is plotted. And in cases where the data is in a particular range for only 1 data point, nothing is plotted at all (can't connect 1 data point with a line and all). The attached image should do better justice to my problem than my description.

Excel help 1.jpg

I found a manual workaround where if I copy the last data point in a bin above the data point in the next bin then the gap is filled. The second image should help explain this part as well.

Excel help 2.jpg

So now I'm trying to figure out how to automate this action and could use some help. Any ideas on how I can accomplish this?

Thanks in advance,
Adam

[dflak]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Bernie Deitrick]

Select all your cells in the data block as one area - columns 4 to the end, and use this macro:

Sub TestMacro2()
Dim rngA As Range

For Each rngA In Selection.SpecialCells(xlCellTypeConstants).Areas
With rngA.Offset(-1).SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "=RC[-3]"
.Value = .Value
End With
Next rngA
End Sub

[abunn3]

Thank you for the response. I'll give this a try. If it's not too much to ask can you help me understand what's going on in the code? I've not delved into writing macros and mostly just work with formulas in the sheets. Thanks.

[Bernie Deitrick]

Basically, you have blocks of data. So my code finds each block of data, finds the blank row above it, and fills those cells with the values from the 3 columns to the left on the same row, using formulas that get converted to values.