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.


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

trying to delete selected range with macro
 

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

trying to delete selected range with macro
 

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

trying to delete selected range with macro
 

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

trying to delete selected range with macro
 

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

trying to delete selected range with macro
 

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

trying to delete selected range with macro
 

Yep, that was it. Apparently there was a space after each unit code.
This code works great!!!

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(Trim(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 Su

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

trying to delete selected range with macro
 

It's a good reminder to always save your workbook before trying (or
experimenting against a copy of the worksheet).

DKY wrote:

Yep, that was it. Apparently there was a space after each unit code.
This code works great!!!

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(Trim(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
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=399226

--

Dave Peterson