Deleting range based on criteria vba

[johnsmith5050]

Hi

I am attempting to use VBA to find a keyword in a column within the active sheet and when it finds this word, to delete the entire row. Once it deletes this row I also need to delete the previous x number of rows, i.e. if keyword is on row 7 and x is 4 it will delete row 7 and the 4 rows prior, so rows 3-6. I have managed to successfully get the first part working as below, it finds the keyword and deletes the row, but I'm not sure what code I need to get it to delete the rows before it. I would really appreciate it if anyone has any suggestions on how to get this working.

Sub DeleteExample()
Dim EX As Long






Application.ScreenUpdating = False

For EX = Range("J" & Rows.Count).End(xlUp).Row To 2 Step -1


If Range("J" & EX).Value = "Example" Then
Rows(EX).EntireRow.delete

End If

Next EX


Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Maybe:

Sub test()
Application.Screenupdating = false
For EX = Range("J" & Rows.Count).End(xlUp).Row To 2 Step -1
    With Range("J" & EX)
    If .Value = "example" Then
        .Offset(-4, 0).Resize(5, 1).EntireRow.Delete
        End If
    End With
Next EX
Application.Screenupdating = true
End Sub

Note this does not have an error trap in the event that the found cell has less than 4 rows above it (i.e. is in row 4 or above).

 

[johnsmith5050]

Maybe:

Sub test()
Application.Screenupdating = false
For EX = Range("J" & Rows.Count).End(xlUp).Row To 2 Step -1
    With Range("J" & EX)
    If .Value = "example" Then
        .Offset(-4, 0).Resize(5, 1).EntireRow.Delete
        End If
    End With
Next EX
Application.Screenupdating = true
End Sub

Note this does not have an error trap in the event that the found cell has less than 4 rows above it (i.e. is in row 4 or above).

Hi

The way the data is structured, the keyword wont be in a row less than row 4.

The code above seems to do the trick thanks, but when I change the offset from -4 to a different number such as -3 it is deleting one extra row after the row the key word is on. I'm guessing its to do with the code .Resize(5,1) as when I change that to (4,1) it works as planned. Can you please explain just for my future understanding what the .Resize part of the code is doing?

Thanks.

 

[JoeMo]

Hi

The way the data is structured, the keyword wont be in a row less than row 4.

The code above seems to do the trick thanks, but when I change the offset from -4 to a different number such as -3 it is deleting one extra row after the row the key word is on. I'm guessing its to do with the code .Resize(5,1) as when I change that to (4,1) it works as planned. Can you please explain just for my future understanding what the .Resize part of the code is doing?

Thanks.

The offset takes you to a cell 4 cells above the found cell. The resize then captures 5 cells beginning with the offset cell and moving down to the found cell. Example, say the found cell is J10, then the offset cell is J6 and the resize is J6:J10 (5 cells).

 

[johnsmith5050]

Hi

Thanks for explaining, that makes sense.

 

[JoeMo]

Hi

Thanks for explaining, that makes sense.

You are welcome - thanks for the reply.