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

 

[broro183]

hi Matt,

Here's another thread with a similar request that should help out (see
my other links in the 4th post of the thread):
'VB - Copy all rows with certain criteria - Excel Help Forum'
(http://tinyurl.com/lbu479)

In one of the links various options are shown for looping (or not)
through multiple criteria.

--------------
I recommend using "Option Explicit" at the top of your modules to
prevent any undeclared variables being created. Also, for the sake of
clarity, I recommend changing your undeclared "count" variable to
something else such as "cll" because "count" is a word with a recognised
meaning (as a property?) in Excel's VBA. You've shown it's use as a
property in your code where you use "rows.count".

hth
Rob

 

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