How to insert new row with dummy info if there is no sequence to item numbers

[pyrte]

Hi there,

I'm working on finding out missing orders in a bunch of sales records. I'm attaching a sample workbook here so that you can understand more of my requirements. Appreciate all the help you can provide.

The sales invoice denotes the sales invoice number at a material level. The item number denotes the line items in the sales invoice. If the sequence is broken then an item was deleted. I need a way to either through formula or macro to be able to find out these missing values, then add a row and fill it with the word deleted record. The item number is in an increment of 10 always. Each sales order item number will start with 10 with an increment of 10.

Looking forward to you guys help and support.

[TABLE="width: 281"]
<tbody>[TR]
[TD][TABLE="width: 529"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Sales Invoice[/TD]
[TD]Item no[/TD]
[TD]Material[/TD]
[TD]Customer[/TD]
[/TR]
[TR]
[TD]1212121[/TD]
[TD]10[/TD]
[TD]125a1211[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1212121[/TD]
[TD]20[/TD]
[TD]85a421[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1212121[/TD]
[TD]30[/TD]
[TD]41a5a5[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1212121[/TD]
[TD]40[/TD]
[TD]a45a15[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1212121[/TD]
[TD]50[/TD]
[TD]1a54a5[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2222131[/TD]
[TD]10[/TD]
[TD]a452a74[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2222131[/TD]
[TD]20[/TD]
[TD]125a1212[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2222131[/TD]
[TD]40[/TD]
[TD]85a422[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2222131[/TD]
[TD]50[/TD]
[TD]41a5a6[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4512432[/TD]
[TD]10[/TD]
[TD]a45a16[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4512432[/TD]
[TD]20[/TD]
[TD]1a54a6[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4512432[/TD]
[TD]30[/TD]
[TD]a452a75[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4512432[/TD]
[TD]50[/TD]
[TD]125a1213[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4512432[/TD]
[TD]60[/TD]
[TD]85a423[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4512432[/TD]
[TD]80[/TD]
[TD]41a5a7[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4512432[/TD]
[TD]90[/TD]
[TD]a45a17[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]8747451[/TD]
[TD]10[/TD]
[TD]1a54a7[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]8747451[/TD]
[TD]20[/TD]
[TD]a452a76[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]8747451[/TD]
[TD]30[/TD]
[TD]125a1214[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]8747451[/TD]
[TD]40[/TD]
[TD]85a424[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]8747451[/TD]
[TD]50[/TD]
[TD]41a5a8[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]8747451[/TD]
[TD]60[/TD]
[TD]a45a18[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 1149"]
<colgroup><col><col><col><col span="12"></colgroup><tbody>[TR]
[TD]Sales Invoice[/TD]
[TD]Item no[/TD]
[TD]Material[/TD]
[TD]Customer[/TD]
[TD]MARKER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]10[/TD]
[TD]125a1211[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]20[/TD]
[TD]85a421[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]#######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]30[/TD]
[TD]41a5a5[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]40[/TD]
[TD]a45a15[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]50[/TD]
[TD]1a54a5[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]10[/TD]
[TD]a452a74[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]20[/TD]
[TD]125a1212[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]40[/TD]
[TD]85a422[/TD]
[TD]B[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]now all you need to do is run down col E and insert blank row where the M's are[/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]50[/TD]
[TD]41a5a6[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]10[/TD]
[TD]a45a16[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]I will post again with that macro in 5 minutes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]20[/TD]
[TD]1a54a6[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]30[/TD]
[TD]a452a75[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]50[/TD]
[TD]125a1213[/TD]
[TD]C[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]60[/TD]
[TD]85a423[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]80[/TD]
[TD]41a5a7[/TD]
[TD]C[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]90[/TD]
[TD]a45a17[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]10[/TD]
[TD]1a54a7[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]20[/TD]
[TD]a452a76[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]30[/TD]
[TD]125a1214[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]40[/TD]
[TD]85a424[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]50[/TD]
[TD]41a5a8[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]60[/TD]
[TD]a45a18[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]#######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=IF(AND(A3=A2,B3-B2<>10),"M","")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG20Jul34
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
[COLOR="Navy"]With[/COLOR] Range("A" & n)
[COLOR="Navy"]If[/COLOR] Not .Offset(1) = "" And .Offset(1) = .Value [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .Offset(1, 1).Value + 10 = .Offset(, 1) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] (.Offset(1, 1).Value - .Offset(, 1)) / 10 > 1 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] R = .Offset(1).Resize(((.Offset(1, 1).Value - .Offset(, 1)) / 10) - 1)
            R.EntireRow.Insert
            R.Offset(-R.Rows.Count).Value = "Deleted Record"
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[oldbrewer]

[TABLE="width: 1149"]
<colgroup><col><col><col><col span="12"></colgroup><tbody>[TR]
[TD]Sales Invoice[/TD]
[TD]Item no[/TD]
[TD]Material[/TD]
[TD]Customer[/TD]
[TD]MARKER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]10[/TD]
[TD]125a1211[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]20[/TD]
[TD]85a421[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]#######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]30[/TD]
[TD]41a5a5[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]40[/TD]
[TD]a45a15[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]50[/TD]
[TD]1a54a5[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]10[/TD]
[TD]a452a74[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]20[/TD]
[TD]125a1212[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]40[/TD]
[TD]85a422[/TD]
[TD]B[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]now all you need to do is run down col E and insert blank row where the M's are[/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]50[/TD]
[TD]41a5a6[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]10[/TD]
[TD]a45a16[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]I will post again with that macro in 5 minutes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]20[/TD]
[TD]1a54a6[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]30[/TD]
[TD]a452a75[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]For j = 3 To 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]50[/TD]
[TD]125a1213[/TD]
[TD]C[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] If Cells(j, 4) = "" Then GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]60[/TD]
[TD]85a423[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"] If Cells(j, 5) = "M" Then GoTo 50 Else GoTo 75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]50 Cells(j, 5).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]80[/TD]
[TD]41a5a7[/TD]
[TD]C[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Selection.EntireRow.Insert[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]90[/TD]
[TD]a45a17[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j = j + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]10[/TD]
[TD]1a54a7[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]75 Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]20[/TD]
[TD]a452a76[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]100 End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]30[/TD]
[TD]125a1214[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]40[/TD]
[TD]85a424[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]50[/TD]
[TD]41a5a8[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]60[/TD]
[TD]a45a18[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]easy to add lines into macro that put "deleted record into the new blank row[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]#######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=IF(AND(A3=A2,B3-B2<>10),"M","")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 1149"]
<tbody>[TR]
[TD]Sales Invoice[/TD]
[TD]Item no[/TD]
[TD]Material[/TD]
[TD]Customer[/TD]
[TD]MARKER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]10[/TD]
[TD]125a1211[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]20[/TD]
[TD]85a421[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]#######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]30[/TD]
[TD]41a5a5[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]40[/TD]
[TD]a45a15[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1212121[/TD]
[TD="align: right"]50[/TD]
[TD]1a54a5[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]10[/TD]
[TD]a452a74[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]20[/TD]
[TD]125a1212[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]THIS RECORD HAS BEEN DELETED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]40[/TD]
[TD]85a422[/TD]
[TD]B[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]now all you need to do is run down col E and insert blank row where the M's are[/TD]
[/TR]
[TR]
[TD="align: right"]2222131[/TD]
[TD="align: right"]50[/TD]
[TD]41a5a6[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]10[/TD]
[TD]a45a16[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]I will post again with that macro in 5 minutes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]20[/TD]
[TD]1a54a6[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]30[/TD]
[TD]a452a75[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]THIS RECORD HAS BEEN DELETED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]FINAL VERSION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]50[/TD]
[TD]125a1213[/TD]
[TD]C[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]60[/TD]
[TD]85a423[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]For j = 3 To 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]THIS RECORD HAS BEEN DELETED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] If Cells(j, 4) = "" Then GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]80[/TD]
[TD]41a5a7[/TD]
[TD]C[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"] If Cells(j, 5) = "M" Then GoTo 50 Else GoTo 75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4512432[/TD]
[TD="align: right"]90[/TD]
[TD]a45a17[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]50 Cells(j, 5).Select[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]10[/TD]
[TD]1a54a7[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Selection.EntireRow.Insert[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]20[/TD]
[TD]a452a76[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Cells(j, 1) = "THIS RECORD HAS BEEN DELETED"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]30[/TD]
[TD]125a1214[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j = j + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]40[/TD]
[TD]85a424[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]75 Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]50[/TD]
[TD]41a5a8[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]100 End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8747451[/TD]
[TD="align: right"]60[/TD]
[TD]a45a18[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]#######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=IF(AND(A3=A2,B3-B2<>10),"M","")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DELETED TEXT IS ON ONE ROW - HONEST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pyrte]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG20Jul34
[COLOR=Navy]Dim[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Num [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] R [COLOR=Navy]As[/COLOR] Range
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR=Navy]For[/COLOR] n = Lst To 2 [COLOR=Navy]Step[/COLOR] -1
[COLOR=Navy]With[/COLOR] Range("A" & n)
[COLOR=Navy]If[/COLOR] Not .Offset(1) = "" And .Offset(1) = .Value [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]If[/COLOR] Not .Offset(1, 1).Value + 10 = .Offset(, 1) [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] (.Offset(1, 1).Value - .Offset(, 1)) / 10 > 1 [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]Set[/COLOR] R = .Offset(1).Resize(((.Offset(1, 1).Value - .Offset(, 1)) / 10) - 1)
            R.EntireRow.Insert
            R.Offset(-R.Rows.Count).Value = "Deleted Record"
        [COLOR=Navy]End[/COLOR] If
   [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

This worked like a charm. Thank you so much for your support. I was really worried how I was going to handle something like for over 10 million records. You just saved me a whole deal of pain. You are simple awesome. I truly appreciate your help.

 

[MickG]

You're very welcome

 

[Ombir]

Hi,

Here is another alternative.

Sub pyrte()
Dim i   As Long

For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) Then
       If (Cells(i, 2) - Cells(i - 1, 2)) / 10 > 1 Then
            Cells(i, 1).EntireRow.insert
            Cells(i, 1) = "Deleted Record"
        End If
    End If
Next
End Sub

 

[oldbrewer]

mine is only 9 lines of code (10 if you need a name for the sub)

 

[pyrte]

Thanks so much oldbrewer, Ombir and MickG. You all have been awesome support. I've learned so much and also solved a problem of having to review of over 1 million records.