Macro to delete a row below a repeating text sting

[dparteka]

Is it possible to create a macro to delete an entire row that is one row below a repeating text string?

A1 has the text [PartSetup]
A2 starts with the text name= then followed by a combination of varying alpha numeric characters, here’s what it might look like.

A1 [PartSetup]
A2 name=PG0031351300-050-B
A3 creator=TranSend Utility
A4 lastModified=05/05/10 10:48:58
A6 name=Tool/Gage
A7 name=Dwg Zone
A8 [PartSetup]
A9 name=PH0031351301-060-C
A10 pieceLabelCount=2
A11 name=Bubble
A12 sgLabelCount=3
A13 charLabelCount=1
A14 charDescriptorCount=5
A15 [PartSetup]
A16 name=WX00413301-160-BC
A15 maxSampleSize=1
A16 reservedSubgroupCount=20

All rows that resemble A2, A9, A16 and so on need to be deleted (not just cleared), the only thing they have in common is the row above which is always [PartSetup]. Note that the text name= is also found in other rows like A6, A7, A11 and so on that should not be delete. The entire range is A1:A2212 and there is no vertical incremental pattern to where [PartSetup] shows up... also, if it helps the row containing [PartSetup] can also be deleted or not deleted.

 

[Somendra Misra]

Hi dparteka,

Try below code:

Sub deleteRow()

Dim lr As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet3")

With ws
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For I = lr To 1 Step -1
    If Cells(I, 1) = "[PartSetup]" Then
    Range("A" & I + 1).Select
        Selection.EntireRow.Delete
    End If
Next I

End Sub

Regards,

 

[dparteka]

SM... once again you have succeeded to amaze, as usual your macro works perfectly, thank you so much

 

[Somendra Misra]

Glad you solve it. Welcome back anytime.

Regards,