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
--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com
tking wrote:
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