Help fine tuning delete macro

  • Thread starter Thread starter mattg
  • Start date Start date
M

mattg

I'm trying to use this mcaro to delete rowsa based on the value of Column B
and it isn't working

Sub DeleteCM()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Set myrange = Range("B2:B" & lastrow)
For Each Count In myrange
If Count.Value = "CM" Then
Count.EntireRow.Delete
End If
Next
End Sub

Any help would be appreciated.

BTW there are 2 other values I want to delete the rows if they exist "IC"
and "MG". Do I need 3 separate macros?
 
Hi,

Try it like this

Sub DeleteCM()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For x = lastrow To 2 Step -1
If UCase(Cells(x, 2)) = "CM" Or UCase(Cells(x, 2)) = _
"IC" Or UCase(Cells(x, 2)) = "MG" Then
Rows(x).EntireRow.Delete
End If
Next
End Sub


Mike
 
Sub DeleteCM()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Set myrange = Range("B2:B" & lastrow)
For Each Count In myrange
If Count.Value = "CM" Or _
Count.Value = "IC" Or _
Count.Value = "MG" Then
Count.EntireRow.Delete
End If
Next
End Sub
 
Option Explicit
Sub DeleteCM()
Dim LastRow As Long
Dim iRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To 2 Step -1
If LCase(.Cells(iRow, "B").Value) = LCase("CM") _
Or LCase(.Cells(iRow, "B").Value) = LCase("IC") _
Or LCase(.Cells(iRow, "B").Value) = LCase("MG") Then
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub

(Untested, uncompiled. watch for typos.)

It's usually lots easier to start from the bottom and work toward the top. Then
you don't have to worry about what row number you're on.)
 
Back
Top