Do I need a Macro?

[tking]

Hello,

I have a worksheet that contains distance intervals (From, To) however
there are gaps between the intervals, (below)

ID From (ft) To (ft)
1 1 4
2 6 8
3 8 12
4 17 20
5 20 25
6 25 30
7 36 40


I would like to fill in the gaps to display a continuous set of
intervals (below).

ID From (ft) To (ft)
1 1 4
**1.5 4 6*
2 6 8
3 8 12
*3.5 12 17
4 17 20
5 20 25
6 25 30
**6.5 30 36*
7 36 40

I think I need a macro to perform this task but any insight would be
helpful.
The macro needs to be able to recognize a gap between a “To” value and
the following “From” value. It then needs to insert a row between the
interval gap and fill in the fields with the appropriate values.

Thank you, thank you, thank you.

Tim K

 

[Dick Kusleika]

Tim:

This seems to work

Sub FillGaps()

Dim lIdx As Long
Dim rRng As Range

With Sheet1
Set rRng = .Range("A2", .Range("A" &
..Rows.Count).End(xlUp).Offset(-1, 0))
End With

For lIdx = rRng.Cells.Count To 1 Step -1
With rRng.Cells(lIdx)
If .Offset(0, 2).Value < .Offset(1, 1).Value Then
.Offset(1, 0).EntireRow.Insert
.Offset(1, 0).Value = .Value + 0.5
.Offset(1, 1).Value = .Offset(0, 2).Value
.Offset(1, 2).Value = .Offset(2, 1).Value
End If
End With
Next lIdx

End Sub