Delete all cells with a "x" in them
 

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

Delete all cells with a "x" in them
 

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


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


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

Delete all cells with a "x" in them
 

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

"scrabtree23" wrote in message
...
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