Do I need a Macro?
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 -- tking ------------------------------------------------------------------------ tking's Profile: http://www.excelforum.com/member.php...o&userid=24811 View this thread: http://www.excelforum.com/showthread...hreadid=383679 |
Do I need a Macro?
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 |
All times are GMT +1. The time now is 09:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com