Macro deletes seperately
 

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

Macro deletes seperately
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zak" wrote in message
...
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

Macro deletes seperately
 

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" wrote:


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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zak" wrote in message
...
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

Macro deletes seperately
 

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

"Zak" wrote in message
...
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" wrote:


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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zak" wrote in message
...
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

Macro deletes seperately
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

"Zak" wrote in message
...
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" wrote:


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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zak" wrote in message
...
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