Deleting range based on criteria vba

johnsmith5050

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

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

johnsmith5050

New Member
Joined
Jun 22, 2014
Messages
7
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,688
Messages
5,597,546
Members
414,154
Latest member
thevaper

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
Top