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

1	250
2	400
3	450
4	600
5	1500
6	1700
7	4000

<tbody>
</tbody>

VBA to sort spreadsheet to:

1	250
2	400
3	450
4	600
#1
5	1500
6	1700
#2
7	4000

<tbody>
</tbody>

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