Deleting range based on criteria vba

johnsmith5050

New Member
Joined
Jun 22, 2014
Messages
8
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
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Maybe:

Code:
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).
 
Last edited:
Upvote 0
Maybe:

Code:
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,214,262
Messages
6,118,549
Members
448,835
Latest member
Profast123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top