Delete specific rows in Excel sheet

Question

I have a table with multiple columns. I would like to delete specific rows within the table. The logic to delete is the following:

If in column B one cell contains a specific value, let's stick to "example" for this case, I would like to delete the following two rows after the row(s) which matched the criteria.

It is important to note that the criteria might appear several times within the table and that the table might have different lengths.

My idea was the following:

1. Identify all rows which contain "example" in column B
2. Store the row numbers in a variable
3. Go through the variable and create a new one which has twice the length of the first one and write the two following rows into the 2nd variable
4. Use the 2nd variable to delete the rows with that numbers.

Unfortunately, I am totally new to VBA and was not able to code it. I also tried to copy code together but I couldn't find a solution for my specific topic.

Answer 1

This is a very slight mod to your approach

• starting from the bottom of column B, work upwards.
• if we encounter "example", delete the two rows below

So if row#7 contains "example", delete row#7 and row#8

Before:

The code:

Sub RowKiller()
    Dim N As Long, i As Long, t As String
    t = "example"
    N = Cells(Rows.Count, "B").End(xlUp).Row

    For i = N To 1 Step -1
      If Cells(i, "B") = t Then
          Range(Cells(i + 1, "B"), Cells(i + 2, "B")).EntireRow.Delete
      End If
    Next i
End Sub

and after:

Answer 2

I think, instead, the best way to handle this is:

1. Loop through all of the populated rows from the last to the first. (this insures we don't pull the rug out from under us when deleting rows).
2. If "Example" is found in column B of that row, delete the two rows after it (we've already traversed those rows so deleting shouldn't be any big deal

3. Thats it.

   Sub deleteRows()

    Dim lastRow as Long
    'get the last row
    lastRow = Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row
   
    'Now work backwards
    Dim i As Long
    For i = lastRow to 1 Step -1 'change that 1 to whatever your first row is
        If Sheet1.Cells(i, 2).value = "Example" Then
            Sheet1.Rows(i + 1 & ":" & i + 2).Delete
        End If
    Next i
   

   End Sub

I haven't tested that, but it looks right. You may have to tweak some things in there, but it will definitely get you in the ballpark.