Macro deletes seperately

[Zak]

I have the following code to do 3 things, when i ran the macro it deleted
just the one thing, then i pressed run again then it did the other thing! it
seemed i had to press run 3 times before the macro executed everything. cant
the macro do them all at once or with just one click?

thanks alot.

Sub Delete_Rows()

Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("I:I"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Text) = "Covansys" _
Or (cell.Text) = "AMS" _
Or (cell.Text) = "Apollo" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.delete
End Sub

 

[Don Guillett]

Try from the bottom up
sub dr()
mc="I"
for i=cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)= "AMS" _
Or cells(i,mc)="Apollo" Then rows(i).delete
next i
end sub

 

[Zak]

Thanks that worked, but i dont understand the logic behind it..and dont
understand why mine had to be clicked on 3 times for it to do all 3 things..?

The code that you gave had two conditions, i want to add more.. up to 10.
how do i do this? if i insert a new line after the first condition to put in
a new 1 it displays error?

please explain how i can have more of the same types of conditions in the
macro. for example, if = microsoft, red tray, isoft (all in column I) then
rows should delete all together.

thanks again.

 

[Don Guillett]

I would have to see the detail and would probably suggest using SELECT CASE.
Send your workbook to my address below, if desired.


Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Sub replace() 'Don
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("i1:i" & lr)
Select Case UCase(c)
Case "CAPULA", "ISOFT", "MICROSOFT", "RED TRAY", _
"HEDRA", "SYSTEM C" _
: c.Offset(, 1) = "AP"

Case Else
End Select
Next
End Sub

Sub Replace1() 'Don could use this instead
Dim r As Range
Set r = Range("I1", Range("I65536").End(xlUp))
For Each c In r
If c = "Capula" Then c.Offset(, 1) = "AP"
If c = "iSOFT" Then c.Offset(, 1) = "AP" 'watch spelling or use
ucase(c)=ISOFT
'or this longer version does the same
If c.Value = "Microsoft" Then c.Offset(0, 1).Value = "AP"
If c.Value = "Red Tray" Then c.Offset(0, 1).Value = "AP"
If c.Value = "Hedra" Then c.Offset(0, 1).Value = "AP"
If c.Value = "System C" Then c.Offset(0, 1).Value = "AP"
Next
End Sub