Find the row a specific text is in a data set & delete certain rows

[bcn1988]

Hello!

I need some code that will do the following:

Find all rows that contain the field names, in this order:

"Oid", "Parent.Name", "LastVersion", "Ideas", "Scope", "Timebox.Name"

Oid is in column A
Parent.Name is in Column B
LastVersion is in Column C
Ideas is in Column D
Scope is in Column E
Timebox.Name is in Column F

Once all rows have been identified, delete this row as well as the two rows that are above the field names.

So if the VBA code has identified row 1000 that contains these field names, delete rows 1000, 999, 998.

There will be around 40 times that these field names will be present and all 3 rows need to be deleted. So if these field names show up in 40 seperate rows independently, then there should be 120 rows that get deleted.

Please let me know if you have any questions. Thank you so much!

[tehneXus]

Hi,

try this example: RemoveOid.xlsm

Contains:

Option Explicit

Const FIRST_DATA_ROW As Long = 2

Public Sub RemoveOid(xlWs As Worksheet)
    Dim aData, i As Long, xlRngDelete As Range, lngLastDataColumn As Long
    With xlWs
        aData = .Range(.Cells(2, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, Columns.Count).End(xlToLeft).Column)).Value2
        lngLastDataColumn = .UsedRange.Columns.Count
        For i = LBound(aData, 1) To UBound(aData, 1)
            If aData(i, 1) = "Oid" And _
                aData(i, 2) = "Parent.Name" And _
                aData(i, 3) = "LastVersion" And _
                aData(i, 4) = "Ideas" And _
                aData(i, 5) = "Scope" And _
                aData(i, 6) = "Timebox.Name" Then
                
                If xlRngDelete Is Nothing Then
                    Set xlRngDelete = .Range(.Cells(i + FIRST_DATA_ROW - 1, 1), .Cells(i + FIRST_DATA_ROW - 3, 6))
                Else
                    Set xlRngDelete = ProperUnion(.Range(.Cells(i + FIRST_DATA_ROW - 1, 1), .Cells(i + FIRST_DATA_ROW - 3, lngLastDataColumn)), xlRngDelete)
                End If
            End If
        Next i
        
        If Not xlRngDelete Is Nothing Then
            xlRngDelete.Delete Shift:=xlUp
            Set xlRngDelete = Nothing
        End If
        Set aData = Nothing
    End With
End Sub

and: http://www.cpearson.com/excel/BetterUnion.aspx

Inform me if you need code explanation.

Regards