trying to delete selected range with macro

[DKY]

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.

Please Login or Register  to view this content.

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

[DKY]

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?

[Dave Peterson]

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

[DKY]

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.

[Dave Peterson]

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

[DKY]

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

[Dave Peterson]

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