trying to delete selected range with macro

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.

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

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

That code seems to delete everything, whether it falls under the category of unit codes or not. I select all my unit codes and run the macro then next thing I know, everything is gone. Am I doing something wrong?

Either that or the macro is!

Did you select just the column with the codes or did you select multiple
columns?



DKY wrote:
>
> That code seems to delete everything, whether it falls under the
> category of unit codes or not. I select all my unit codes and run the
> macro then next thing I know, everything is gone. Am I doing something
> wrong?
>
> --
> 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

I selected the cells that have the codes not the column (I didnt want to delete the row with the header) I hope that's not a problem. But the cells I'm selecting are all in the same column.

Are you sure that your codes to save are part of this list?

"2A", "7G", "D1", "D2", "D6", _
"F3", "H1", "H5", "M4", "M6", "G5"

Any extra spaces in those codes?

If there are, then you could use this:

Select Case UCase(trim(myCell.Value))
instead of:
Select Case UCase(myCell.Value)



DKY wrote:
>
> I selected the cells that have the codes not the column (I didnt want to
> delete the row with the header) I hope that's not a problem. But the
> cells I'm selecting are all in the same column.
>
> --
> 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