Help fine tuning delete macro

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

 

[r]

Sub DeleteCM()
Dim r As Long
Dim lastrow As Long
Dim v
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For r = lastrow To 2 Step -1
v = Cells(r, "B")
If v = "CM" Or v = "IC" Or v = "MG" Then
Cells(r, "B").EntireRow.Delete
End If
Next
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Mike H]

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

 

[Jacob Skaria]

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

 

[Dave Peterson]

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.)