Macro does not delete all the required rows

[AG]

Hi Everyone,

I have a datasheet (a daily report that I get from a system). In this
report I have column AE that contains the identifier for that row.
Some of the identifiers are DUMMY. These are wrong postings from the
system. I have written a following code to delete all rows that have
DUMMY in column AE.

Sub delete_dummy()
Dim Cell As Range
For each Cell in Range("AE:AE")
If Cell.Value = "DUMMY" Then
Cell.EntireRow.Delete
End If
Next Cell
End Sub

The code works but it does not delete all the rows containing dummy. I
have to run it a few times before all the dummy rows are deleted. I
have no idea why that happens and how to fix it so that all the dummy
items are deleted in one go. Can someone help me with this. Thanks
very much.

Regards,
-AG

 

[JLGWhiz]

You need to start at the bottom and work to the top
when deleting multiple row. It automatically shifts
up after deleting the row so that you skip rows if you
go top to bottom. Try this.

Sub delete_dummy()
Dim i As Range, lstRow As Long
LstRow = Cells(Rows.Count, 31).End(xlUp).Row
sRng = Range("AE1:AE" & lstRow)
For i = lstRow To 1 Step *1
If i.Value = "DUMMY" Then
i.EntireRow.Delete
End If
Next
End Sub

 

[JLGWhiz]

I know better than that. Use this:

Sub delete_dummy()
Dim i As Range, lstRow As Long
LstRow = Cells(Rows.Count, 31).End(xlUp).Row
For i = lstRow To 1 Step -1
IfCells( i, 31).Value = "DUMMY" Then
Cells(i, 31).EntireRow.Delete
End If
Next
End Sub

 

[AG]

Hi JLGWhiz,
Thanks very much for the help. It works fine. I think you meant
i As Integer and not Range.

I will be interested to know why it does not work when you go from top
to bottom. I am sure you would know.
Thanks again for your help.

Regards,
-AG