samilynn
Board Regular
- Joined
- Jun 24, 2003
- Messages
- 168
- Office Version
- 2016
- Platform
- Windows
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
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