When you find a value to delete (say on row 3), then delete it, everything
shifts up one row. So the next cell you're looking at in your code will be in
row 4--but row 4 has been shifted to row 3 and you've skipped that entry.

It's usually easier to start at the bottom of the range and work up--or to build
a range of cells to delete and delete them at the end.

And there are other ways to deal with a long list of strings to keep. One of
those other ways is using a "select case" structure:

Option Explicit
Public Sub COLUMN_UNIT_CODE()
Dim myCell As Range
Dim delRng As Range

'Application.ScreenUpdating = False
For Each myCell In Selection.Cells
Select Case UCase(myCell.Value)
Case Is = "2A", "7G", "D1", "D2", "D6", _
"F3", "H1", "H5", "M4", "M6", "G5"
'Do nothing, keep it
Case Else
If delRng Is Nothing Then
Set delRng = myCell
Else
Set delRng = Union(myCell, delRng)
End If
End Select
Next myCell

If delRng Is Nothing Then
'nothing found, do nothing
Else
delRng.EntireRow.Delete
End If

End Sub




DKY wrote:
>
> I can't figure out why this macro isn't working right. In my group we
> have user id's which are letter and number combos. I wanted to write
> up a macro in which I can take any list any size and select the user
> id's and it will filter the results to just our id's. I wrote this but
> it seems as though it's skipping rows. I can't figure it out.
>
> Code:
> --------------------
> Option Explicit
> Public Sub COLUMN_UNIT_CODE()
> Dim cell
> 'Application.ScreenUpdating = False
> For Each cell In Selection
> cell.NumberFormat = "@"
> If cell.Value <> "2A" Then
> If cell.Value <> "7G" Then
> If cell.Value <> "D1" Then
> If cell.Value <> "D2" Then
> If cell.Value <> "D6" Then
> If cell.Value <> "F3" Then
> If cell.Value <> "H1" Then
> If cell.Value <> "H5" Then
> If cell.Value <> "M1" Then
> If cell.Value <> "M4" Then
> If cell.Value <> "M6" Then
> If cell.Value <> "G5" Then
> cell.EntireRow.delete
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> Next cell
> End Sub
> --------------------
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=399226

--

Dave Peterson