I am trying to run a macro that will delete row if a certain keyword is
spotted, but I have to run the macro multiple times before it cleans out the
spreadsheet completley. Below is what I have.

Sub Delete()
Dim myRange As Range
Dim iLastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("G2:G" & LastRow)
For Each c In myRange
c.Select
If c.Value = "FUNDED" Or c.Value = "DOCS-OUT" Or c.Value = "PURCHASED" Then
ActiveCell.EntireRow.Select
Selection.Delete
End If
Next
End Sub

You should have worked from the bottom up

Sub Delete()

Dim iLastRow As Long
for i=Cells(Rows.Count, "A").End(xlUp).Row to 2 step-1

mc-cells(i,"g")
'one line below
If mc = "FUNDED" Or mc= "DOCS-OUT" Or mc= "PURCHASED" Then rows(i).delete
'one line above

Next i
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TMc21" wrote in message
...
I am trying to run a macro that will delete row if a certain keyword is
spotted, but I have to run the macro multiple times before it cleans out
the
spreadsheet completley. Below is what I have.

Sub Delete()
Dim myRange As Range
Dim iLastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("G2:G" & LastRow)
For Each c In myRange
c.Select
If c.Value = "FUNDED" Or c.Value = "DOCS-OUT" Or c.Value = "PURCHASED"
Then
ActiveCell.EntireRow.Select
Selection.Delete
End If
Next
End Sub

Your problem is when you move down the worksheet and delete a row you are
skipping a row. When ever you have two consecutive row that need to be
deleted the 2nd is being skipped. You have to move up the worksheet.


Sub Delete()
Dim myRange As Range
Dim iLastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = LastRow To 1 Step -1
With Range("G" & RowCount)
If .Value = "FUNDED" Or .Value = "DOCS-OUT" Or .Value = "PURCHASED" Then
Rows(RowCount).Delete
End If
End With
Next RowCount
End Sub


"TMc21" wrote:

I am trying to run a macro that will delete row if a certain keyword is
spotted, but I have to run the macro multiple times before it cleans out the
spreadsheet completley. Below is what I have.

Sub Delete()
Dim myRange As Range
Dim iLastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("G2:G" & LastRow)
For Each c In myRange
c.Select
If c.Value = "FUNDED" Or c.Value = "DOCS-OUT" Or c.Value = "PURCHASED" Then
ActiveCell.EntireRow.Select
Selection.Delete
End If
Next
End Sub

You can not use a for next loop and then delete stuff in the middle of the
range. Youre results will be unpredictable. There are 2 choices. One is to
travel up from the bottom cell to the top deleting as you go or you can creae
a single large range to be deleted once you exit the for next...

Here is the one large delete option with your existing code...

Sub Delete()
Dim myRange As Range
dim rngToDelete as range
Dim iLastRow As Long
dim c as range

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("G2:G" & LastRow)
For Each c In myRange
If c.Value = "FUNDED" Or c.Value = "DOCS-OUT" Or c.Value = "PURCHASED" Then
if rngtodelete is nothing then
set rngtodelete = c
else
set rngtodelete = union(c, rngtodelete)
end if
End If
Next c
if not rngtodelete is nothing then rngtodelete.entirerow.delete
End Sub

--
HTH...

Jim Thomlinson


"TMc21" wrote:

I am trying to run a macro that will delete row if a certain keyword is
spotted, but I have to run the macro multiple times before it cleans out the
spreadsheet completley. Below is what I have.

Sub Delete()
Dim myRange As Range
Dim iLastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("G2:G" & LastRow)
For Each c In myRange
c.Select
If c.Value = "FUNDED" Or c.Value = "DOCS-OUT" Or c.Value = "PURCHASED" Then
ActiveCell.EntireRow.Select
Selection.Delete
End If
Next
End Sub

The problem is when you delete the row it moves the below row up one and then
goes to the next row. The below code does what you are looking for just try
it.


Sub Delete()

Dim strCheck As String
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For c = 1 To iLastRow
strCheck = Range("G" & c).Value
If strCheck = "FUNDED" Or strCheck = "DOCS-OUT" Or strCheck = "PURCHASED"
Then
Rows(c).Select
Selection.Delete
c = c - 1
End If
Next

End Sub

TMc21 wrote:
I am trying to run a macro that will delete row if a certain keyword is
spotted, but I have to run the macro multiple times before it cleans out the
spreadsheet completley. Below is what I have.

Sub Delete()
Dim myRange As Range
Dim iLastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("G2:G" & LastRow)
For Each c In myRange
c.Select
If c.Value = "FUNDED" Or c.Value = "DOCS-OUT" Or c.Value = "PURCHASED" Then
ActiveCell.EntireRow.Select
Selection.Delete
End If
Next
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200808/1

With my reply below you may want to add the the below in or else you may get
sick watching it.

After Sub Delete() add Application.ScreenUpdating = False
After Next add Application.ScreenUpdating = True

dustinbrearton wrote:
The problem is when you delete the row it moves the below row up one and then
goes to the next row. The below code does what you are looking for just try
it.

Sub Delete()

Dim strCheck As String
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For c = 1 To iLastRow
strCheck = Range("G" & c).Value
If strCheck = "FUNDED" Or strCheck = "DOCS-OUT" Or strCheck = "PURCHASED"
Then
Rows(c).Select
Selection.Delete
c = c - 1
End If
Next

End Sub

I am trying to run a macro that will delete row if a certain keyword is
spotted, but I have to run the macro multiple times before it cleans out the
[quoted text clipped - 13 lines]
Next
End Sub

--
Message posted via http://www.officekb.com