ExcelBanter - View Single Post - trying to delete selected range with macro

Dave Peterson

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