VBA loop to insert row based on numeric range

[alec_DLW]

Hi,

I am new to this forum and need some help to insert rows based on criteria in a spreadsheet (csv) with 40 columns and <100,000 rows.

I need to insert a row based on a range of +- 600 such that if B2 is within +- 600 of B1 DO NOT insert row. If B2 > B1 by >600 THEN insert row and mark row with increasing numbers 1,2,3,4.... to count inserted rows. This needs to done throughout entire spreadsheet.

An example is below
[TABLE="width: 200"]
<tbody>[TR]
[TD]1[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1700[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4000[/TD]
[/TR]
</tbody>[/TABLE]

VBA to sort spreadsheet to:
[TABLE="width: 200"]
<tbody>[TR]
[TD]1[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1700[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Momentman]

This code inserts the rows without the numbering, do the numberings need to have the # symbols?

Sub InsertRows()
    For I = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Abs(Range("B" & I) - Range("B" & I - 1)) >= 600 Then
            Rows(I).Resize(1).Insert
        End If
    Next I
End Sub

Run on sample data

 

[alec_DLW]

Thanks Momentman.

No the the numberings do not need to the # symbol. I am new to VBA and am not sure if I can run the code you have posted as is? I seem to get an error for the line "If Abs(Range.....)"

Thanks again

 

[Momentman]

A few things, confirm your data as posted in columns A and B 'cos i ran the code and it works fine

Do your columns have headers? If they do, change the "2" un the For I =.... to "3"

For I = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1