Hi Experts:

I have used this macro before and it worked perfectly...however, now when it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Jeannie,

Is automatic calculation turned on? Is it recalculating after each row is
deleted (watch the status bar in the bottom left corner of the XL
window...It will say something like "Calculating" and give a percentage)?
Does this workbook/worksheet take some time to recalculate?

If that is your issue, then you can turn AutoCalc off by putting this as one
of the first lines in your macro:

Application.Calculation = xlCalculationManual

.... and then turn it back on by putting this as the last line in the macro:

Application.Calculation = xlCalculationAutomatic

HTH,

Conan

"jeannie v" wrote in message
...
Hi Experts:

I have used this macro before and it worked perfectly...however, now when
it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro
and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Quit selecting for one thing and work from the bottom up when deleting rows.
End if not needed here. You are also probably looking in more rows than
necessary, or desirable. Try this, AS WRITTEN

mc="J"
For i = cells(rows.count,mc).end(xlup).row to 1 step-1
If Cells(i,mc) = "LOA" Or Cells(i,mc) = "Termed" Or _
Cells(i,mc) = "Duplicate" Then Rows(i).delete
Next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jeannie v" wrote in message
...
Hi Experts:

I have used this macro before and it worked perfectly...however, now when
it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro
and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Hi Conan:

Whoa.....thank you...it works just as it's suppose to with your
recommendation......I am truly grateful....I will remember that for future
applications....

I have another favor to ask....If I want this macro to just delete the rows
that have a 0 in Column I, how can I revise the macro to do that?

Any help would be greatly appreciated!
--
jeannie v


"Conan Kelly" wrote:

Jeannie,

Is automatic calculation turned on? Is it recalculating after each row is
deleted (watch the status bar in the bottom left corner of the XL
window...It will say something like "Calculating" and give a percentage)?
Does this workbook/worksheet take some time to recalculate?

If that is your issue, then you can turn AutoCalc off by putting this as one
of the first lines in your macro:

Application.Calculation = xlCalculationManual

.... and then turn it back on by putting this as the last line in the macro:

Application.Calculation = xlCalculationAutomatic

HTH,

Conan

"jeannie v" wrote in message
...
Hi Experts:

I have used this macro before and it worked perfectly...however, now when
it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro
and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Hi Don:

Thank you for your solution to my problem....I have tried both yours and
Conan's resolutions and both give me what I want......

I appreciate your expertise...Thank you!
--
jeannie v


"Don Guillett" wrote:

Quit selecting for one thing and work from the bottom up when deleting rows.
End if not needed here. You are also probably looking in more rows than
necessary, or desirable. Try this, AS WRITTEN

mc="J"
For i = cells(rows.count,mc).end(xlup).row to 1 step-1
If Cells(i,mc) = "LOA" Or Cells(i,mc) = "Termed" Or _
Cells(i,mc) = "Duplicate" Then Rows(i).delete
Next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jeannie v" wrote in message
...
Hi Experts:

I have used this macro before and it worked perfectly...however, now when
it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro
and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Try this one. Looks a little faster.

Option Compare Text
Sub DeleteLOATermedDupRecords()
Dim rng1 As Range
check_words = Array("LOA", "Termed", "Duplicate")
Set rng1 = Range("J1", Cells(Rows.Count, "J").End(xlUp))
For i = LBound(check_words) To UBound(check_words)
For Each cell In rng1
If InStr(1, cell.Value, check_words(i)) Then
cell.EntireRow.Delete
End If
Next
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Feb 2008 17:24:01 -0800, jeannie v
wrote:

Hi Experts:

I have used this macro before and it worked perfectly...however, now when it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.

Hi Gord:

Excellent!!!!!! Thank you so much! I have a favor to ask: How can I revise
this Macro to only delete the rows that have a "Zero" in Column I for the
LOA, Termed and Dups in Column J?

I would appreciate any help you can provide,
--
jeannie v


"Gord Dibben" wrote:

Try this one. Looks a little faster.

Option Compare Text
Sub DeleteLOATermedDupRecords()
Dim rng1 As Range
check_words = Array("LOA", "Termed", "Duplicate")
Set rng1 = Range("J1", Cells(Rows.Count, "J").End(xlUp))
For i = LBound(check_words) To UBound(check_words)
For Each cell In rng1
If InStr(1, cell.Value, check_words(i)) Then
cell.EntireRow.Delete
End If
Next
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Feb 2008 17:24:01 -0800, jeannie v
wrote:

Hi Experts:

I have used this macro before and it worked perfectly...however, now when it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.

Hi Experts:

I was able to figure this one out and I was able to revise my Macro to make
it work the way I wanted it....

Thank you all for your help!
--
jeannie v


"jeannie v" wrote:

Hi Experts:

I have used this macro before and it worked perfectly...however, now when it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Jeannie,

You want rows deleted ONLY when there is a 0 (zero) in column I? What about
column J being LOA, Termed, or Duplicate? Is zero stored as text or as a
number?

For just 0 as a number:


Sub DeleteLOATermedDupRecords()
Application.Calculation = xlCalculationManual
For i = 1 To 12000
If Cells(i, "I") = 0 Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i
Application.Calculation = xlCalculationAutomatic
End Sub



For just 0 as text:


Sub DeleteLOATermedDupRecords()
Application.Calculation = xlCalculationManual
For i = 1 To 12000
If Cells(i, "I") = "0" Then '<--Quotes around the zero
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i
Application.Calculation = xlCalculationAutomatic
End Sub



If you want to include column J criteria, then you'll have to add your
previous statements with the correct combination of OR's & AND's, depending
on your requierments.

But Don Guillett's solution is more efficent. His suggestion for working
from the bottom up when deleting cells is right on, although I did see that
you compensated for the deleted row with "i = i - 1" inside the loop. Also
he suggests that you do not select the row you want to first, just delete it
without selecting it. That is another good suggestion. Remember, you don't
need to select any cells to make almost any changes you can think of.
Almost everything (if not everything) can be done without selecting.

So, to edit his code to accomplish what I did above:


Application.Calculation = xlCalculationManual
mc="J"
For i = cells(rows.count,mc).end(xlup).row to 1 step-1
If Cells(i,"I") = 0 Then 'for zero stored as a number (comment for
0 as text)
'If Cells(i,"I") = 0 Then 'uncomment this line for zero as text
Rows(i).delete
end if
Next i
Application.Calculation = xlCalculationAutomatic




HTH,

Conan




"jeannie v" wrote in message
...
Hi Conan:

Whoa.....thank you...it works just as it's suppose to with your
recommendation......I am truly grateful....I will remember that for future
applications....

I have another favor to ask....If I want this macro to just delete the
rows
that have a 0 in Column I, how can I revise the macro to do that?

Any help would be greatly appreciated!
--
jeannie v


"Conan Kelly" wrote:

Jeannie,

Is automatic calculation turned on? Is it recalculating after each row
is
deleted (watch the status bar in the bottom left corner of the XL
window...It will say something like "Calculating" and give a percentage)?
Does this workbook/worksheet take some time to recalculate?

If that is your issue, then you can turn AutoCalc off by putting this as
one
of the first lines in your macro:

Application.Calculation = xlCalculationManual

.... and then turn it back on by putting this as the last line in the
macro:

Application.Calculation = xlCalculationAutomatic

HTH,

Conan

"jeannie v" wrote in message
...
Hi Experts:

I have used this macro before and it worked perfectly...however, now
when
it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the
macro
and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Jeannie,

I just saw your resonse to Gord Dibben's reply.

Use something like this for your "IF" statement:

If Cells(i, "I") = 0 AND _
(Cells(i, "J") = "LOA" Or _
Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate") Then

Hopefully that should work for you.

HTH,

Conan






"Conan Kelly" wrote in message
...
Jeannie,

You want rows deleted ONLY when there is a 0 (zero) in column I? What
about column J being LOA, Termed, or Duplicate? Is zero stored as text or
as a number?

For just 0 as a number:


Sub DeleteLOATermedDupRecords()
Application.Calculation = xlCalculationManual
For i = 1 To 12000
If Cells(i, "I") = 0 Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i
Application.Calculation = xlCalculationAutomatic
End Sub



For just 0 as text:


Sub DeleteLOATermedDupRecords()
Application.Calculation = xlCalculationManual
For i = 1 To 12000
If Cells(i, "I") = "0" Then '<--Quotes around the zero
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i
Application.Calculation = xlCalculationAutomatic
End Sub



If you want to include column J criteria, then you'll have to add your
previous statements with the correct combination of OR's & AND's,
depending on your requierments.

But Don Guillett's solution is more efficent. His suggestion for working
from the bottom up when deleting cells is right on, although I did see
that you compensated for the deleted row with "i = i - 1" inside the loop.
Also he suggests that you do not select the row you want to first, just
delete it without selecting it. That is another good suggestion.
Remember, you don't need to select any cells to make almost any changes
you can think of. Almost everything (if not everything) can be done
without selecting.

So, to edit his code to accomplish what I did above:


Application.Calculation = xlCalculationManual
mc="J"
For i = cells(rows.count,mc).end(xlup).row to 1 step-1
If Cells(i,"I") = 0 Then 'for zero stored as a number (comment
for 0 as text)
'If Cells(i,"I") = 0 Then 'uncomment this line for zero as text
Rows(i).delete
end if
Next i
Application.Calculation = xlCalculationAutomatic




HTH,

Conan




"jeannie v" wrote in message
...
Hi Conan:

Whoa.....thank you...it works just as it's suppose to with your
recommendation......I am truly grateful....I will remember that for
future
applications....

I have another favor to ask....If I want this macro to just delete the
rows
that have a 0 in Column I, how can I revise the macro to do that?

Any help would be greatly appreciated!
--
jeannie v


"Conan Kelly" wrote:

Jeannie,

Is automatic calculation turned on? Is it recalculating after each row
is
deleted (watch the status bar in the bottom left corner of the XL
window...It will say something like "Calculating" and give a
percentage)?
Does this workbook/worksheet take some time to recalculate?

If that is your issue, then you can turn AutoCalc off by putting this as
one
of the first lines in your macro:

Application.Calculation = xlCalculationManual

.... and then turn it back on by putting this as the last line in the
macro:

Application.Calculation = xlCalculationAutomatic

HTH,

Conan

"jeannie v" wrote in message
...
Hi Experts:

I have used this macro before and it worked perfectly...however, now
when
it
searches and deletes the rows that I want it to, it takes forever to
go
through the document....It used to go through very fast and deleted
the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the
macro
and
it takes a long time to complete...What might be wrong with this
Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Post your final code.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jeannie v" wrote in message
...
Hi Experts:

I was able to figure this one out and I was able to revise my Macro to
make
it work the way I wanted it....

Thank you all for your help!
--
jeannie v


"jeannie v" wrote:

Hi Experts:

I have used this macro before and it worked perfectly...however, now when
it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro
and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Hi Gord:

This isn't working as it should...It deletes some of the Zero Records in I
for LOA, Termed and Duplicate, but not all of them.

Can you help me revise this to work correctly? I appreciate your expertise.

I want to delete all rows that have Zero in Column I and have LOA, Termed or
Duplicate in Column J....If there is a "1" or greater # in Column I, I want
the record to remain. I'm not sure waht is wrong with this.

Option Compare Text
Sub DeleteLOATermedDupRecords()
Application.Calculation = xlCalculationManual
Dim rng1 As Range
check_words = Array("LOA", "Termed", "Duplicate")
Set rng1 = Range("J1", Cells(Rows.Count, "J").End(xlUp))
For i = LBound(check_words) To UBound(check_words)
For Each cell In rng1
If InStr(1, cell.Value, check_words(i)) Then
cell.EntireRow.Delete
End If
Next
Next
Application.Calculation = xlCalculationAutomatic
End Sub
--
jeannie v


"Gord Dibben" wrote:

Try this one. Looks a little faster.

Option Compare Text
Sub DeleteLOATermedDupRecords()
Dim rng1 As Range
check_words = Array("LOA", "Termed", "Duplicate")
Set rng1 = Range("J1", Cells(Rows.Count, "J").End(xlUp))
For i = LBound(check_words) To UBound(check_words)
For Each cell In rng1
If InStr(1, cell.Value, check_words(i)) Then
cell.EntireRow.Delete
End If
Next
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Feb 2008 17:24:01 -0800, jeannie v
wrote:

Hi Experts:

I have used this macro before and it worked perfectly...however, now when it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.

Go back and have a look at your original post and the macro you said did not
work.

Not a word about zeros in Column L

My macro was designed to do what you wanted in your post.....delete rows with
LOA, Duplicate or Termed.

I think you are replying to wrong guy. I did not respond to your second request
about the zeros.

You want to be replying to Conan whom you asked for "another favor".

He gave you some code to take care of that on Feb 18th at 6:51PM


Gord

On Sat, 23 Feb 2008 16:55:02 -0800, jeannie v
wrote:

Hi Gord:

This isn't working as it should...It deletes some of the Zero Records in I
for LOA, Termed and Duplicate, but not all of them.

Can you help me revise this to work correctly? I appreciate your expertise.

I want to delete all rows that have Zero in Column I and have LOA, Termed or
Duplicate in Column J....If there is a "1" or greater # in Column I, I want
the record to remain. I'm not sure waht is wrong with this.

Option Compare Text
Sub DeleteLOATermedDupRecords()
Application.Calculation = xlCalculationManual
Dim rng1 As Range
check_words = Array("LOA", "Termed", "Duplicate")
Set rng1 = Range("J1", Cells(Rows.Count, "J").End(xlUp))
For i = LBound(check_words) To UBound(check_words)
For Each cell In rng1
If InStr(1, cell.Value, check_words(i)) Then
cell.EntireRow.Delete
End If
Next
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Good Evening, Gord:

You are correct and I trust your expertise...Thank you for all your help...I
had so many replies that I became confused: I did finally come up with the
following that seems to work just fine:

Sub DeleteLOATermedDupRecords()
'
mc = "J"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -1
If Cells(i, "I") = "0" Then
If Cells(i, mc) = "LOA" _
Or Cells(i, mc) = "Termed" _
Or Cells(i, mc) = "Duplicate" Then
Rows(i).Delete
End If
End If
Next i
'
End Sub

--
jeannie v


"Gord Dibben" wrote:

Go back and have a look at your original post and the macro you said did not
work.

Not a word about zeros in Column L

My macro was designed to do what you wanted in your post.....delete rows with
LOA, Duplicate or Termed.

I think you are replying to wrong guy. I did not respond to your second request
about the zeros.

You want to be replying to Conan whom you asked for "another favor".

He gave you some code to take care of that on Feb 18th at 6:51PM


Gord

On Sat, 23 Feb 2008 16:55:02 -0800, jeannie v
wrote:

Hi Gord:

This isn't working as it should...It deletes some of the Zero Records in I
for LOA, Termed and Duplicate, but not all of them.

Can you help me revise this to work correctly? I appreciate your expertise.

I want to delete all rows that have Zero in Column I and have LOA, Termed or
Duplicate in Column J....If there is a "1" or greater # in Column I, I want
the record to remain. I'm not sure waht is wrong with this.

Option Compare Text
Sub DeleteLOATermedDupRecords()
Application.Calculation = xlCalculationManual
Dim rng1 As Range
check_words = Array("LOA", "Termed", "Duplicate")
Set rng1 = Range("J1", Cells(Rows.Count, "J").End(xlUp))
For i = LBound(check_words) To UBound(check_words)
For Each cell In rng1
If InStr(1, cell.Value, check_words(i)) Then
cell.EntireRow.Delete
End If
Next
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Good Evening, Don:

Thank you for all your help....This is the Macro I finally ended up with
that seems to work fine:

Sub DeleteLOATermedDupRecords()
'
mc = "J"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -1
If Cells(i, "I") = "0" Then
If Cells(i, mc) = "LOA" _
Or Cells(i, mc) = "Termed" _
Or Cells(i, mc) = "Duplicate" Then
Rows(i).Delete
End If
End If
Next i
'
End Sub

--
jeannie v


"Don Guillett" wrote:

Post your final code.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jeannie v" wrote in message
...
Hi Experts:

I was able to figure this one out and I was able to revise my Macro to
make
it work the way I wanted it....

Thank you all for your help!
--
jeannie v


"jeannie v" wrote:

Hi Experts:

I have used this macro before and it worked perfectly...however, now when
it
searches and deletes the rows that I want it to, it takes forever to go
through the document....It used to go through very fast and deleted the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the macro
and
it takes a long time to complete...What might be wrong with this Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jeannie v" wrote in message
...
Good Evening, Don:

Thank you for all your help....This is the Macro I finally ended up with
that seems to work fine:

Sub DeleteLOATermedDupRecords()
'
mc = "J"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -1
If Cells(i, "I") = "0" Then
If Cells(i, mc) = "LOA" _
Or Cells(i, mc) = "Termed" _
Or Cells(i, mc) = "Duplicate" Then
Rows(i).Delete
End If
End If
Next i
'
End Sub

--
jeannie v


"Don Guillett" wrote:

Post your final code.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jeannie v" wrote in message
...
Hi Experts:

I was able to figure this one out and I was able to revise my Macro to
make
it work the way I wanted it....

Thank you all for your help!
--
jeannie v


"jeannie v" wrote:

Hi Experts:

I have used this macro before and it worked perfectly...however, now
when
it
searches and deletes the rows that I want it to, it takes forever to
go
through the document....It used to go through very fast and deleted
the
rows,
but now it deletes one row at a time very slowly....I have about 10000
records for it to go through and delete the records that are in the
macro
and
it takes a long time to complete...What might be wrong with this
Macro?

Sub DeleteLOATermedDupRecords()
'
' DeleteLOATermedDupRecords Macro
' Macro recorded 2/17/2008 by Jeannie Vincovich
'
For i = 1 To 12000
If Cells(i, "J") = "LOA" Or Cells(i, "J") = "Termed" Or _
Cells(i, "J") = "Duplicate" Then
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next i

End Sub


Any help you can provide would be greatly appreciated.
--
jeannie v

Thanks.

I have been following your other "missing a command" thread and note the
progression to a solution.


Gord

On Sat, 23 Feb 2008 20:40:02 -0800, jeannie v
wrote:

Good Evening, Gord:

You are correct and I trust your expertise...Thank you for all your help...I
had so many replies that I became confused: I did finally come up with the
following that seems to work just fine:

Sub DeleteLOATermedDupRecords()
'
mc = "J"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -1
If Cells(i, "I") = "0" Then
If Cells(i, mc) = "LOA" _
Or Cells(i, mc) = "Termed" _
Or Cells(i, mc) = "Duplicate" Then
Rows(i).Delete
End If
End If
Next i
'
End Sub