Delete all cells with a "x" in them

  • Thread starter Thread starter scrabtree23
  • Start date Start date
S

scrabtree23

I want to delete all cells in a range that have "x" in
them. I have used the following code:

For Each Cell In Sheets("CDPTS").Range("B1:B363")
If Cell.Value = "x" Then
Cell.Delete Shift:=xlUp
End If
Next Cell

However, this doesn't delete them all? I repeated the
code several times and it finally does get them all. Is
there a more reliable code to do my job?

SDC
 
SDC,

Build up a range that includes all the x values before doing the deletion:

Sub DeleteXs()
Dim myRange As Range
Dim myCell As Range

For Each myCell In Sheets("CDPTS").Range("B1:B363")
If myCell.Value = "x" Then
If myRange Is Nothing Then
Set myRange = myCell
Else
Set myRange = Union(myRange, myCell)
End If
End If
Next myCell

myRange.Delete Shift:=xlUp

End Sub

HTH,
Bernie
Excel MVP
 
Try,

for i = 363 to 1 step -1
If Sheets("CDPTS").Range("B" & i).Value = "x" Then
Sheets("CDPTS").Range("B" & i).Delete Shift:=xlUp
End If
Next Cell

Cecil
 
Back
Top