Delete entire rows modify?

samilynn

Board Regular
Joined
Jun 24, 2003
Messages
145
I am using this code to delete all rows that contain "x" in Column O.
Can I modify it to delete rows that contain X, Y or Z in that column?

Thank you!

Sub DeleteRowIfCellX()

Const MyTarget = "x"

Dim Rng As Range, DelCol As New Collection, x
Dim i As Long, j As Long, k As Long

' Calc last row number
j = Range("O" & Rows.Count).End(xlUp).Row

' Collect rows range with MyTarget
For i = 1 To j
If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then
k = k + 1
If k = 1 Then
Set Rng = Rows(i)
Else
Set Rng = Union(Rng, Rows(i))
If k >= 100 Then
DelCol.Add Rng
k = 0
End If
End If
End If
Next
If k > 0 Then DelCol.Add Rng

' Delete rows with MyTarget
For Each x In DelCol
x.Delete
Next

' Update UsedRange
With ActiveSheet.UsedRange: End With

' Restore screen updating and events
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Nine Zero

Well-known Member
Joined
Mar 10, 2016
Messages
622
Change this line

Code:
If WorksheetFunction.CountIfs(Rows(i), MyTarget) > 0 Then

with this
Code:
If WorksheetFunction.CountIfs(Rows(i), "x") > 0 Or WorksheetFunction.CountIfs(Rows(i), "y") > 0 Or WorksheetFunction.CountIfs(Rows(i), "z") > 0 Then
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,011
Office Version
  1. 365
Platform
  1. Windows
Another option
Code:
Sub delRws()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("X", "Y", "Z")
   For i = 0 To UBound(Ary)
      Range("O:O").Replace Ary(i), "=XXX", xlWhole, , False, , False, False
   Next i
   Range("O:O").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End Sub
 

Nine Zero

Well-known Member
Joined
Mar 10, 2016
Messages
622
@Fluff this is much cleaner I just personally have not been able to wrap my head around Arrays in VBA ... Java and Ruby are my main languages and they are easy for me to grasp.... time to study VBA Arrays
 

Nine Zero

Well-known Member
Joined
Mar 10, 2016
Messages
622
Change this line

Code:
If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then

with this
Code:
If WorksheetFunction.CountIf(Rows(i), "x") > 0 Or WorksheetFunction.CountIf(Rows(i), "y") > 0 Or WorksheetFunction.CountIf(Rows(i), "z") > 0 Then

EDIT: REALIZED I ACCIDENTALLY PUT AN "s" IN THE COUNTIF..... REMOVED IN THIS
 

Forum statistics

Threads
1,148,294
Messages
5,745,936
Members
423,985
Latest member
sayed manzar

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