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

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" structu

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 th
category of unit codes or not. I select all my unit codes and run th
macro then next thing I know, everything is gone. Am I doing somethin
wrong

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=39922

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=399226

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