Sleeper: Delete row when condition is true

[outrider]

I am having problems with a project where the worksheet has 400 rows, if any cells in column "G" contain a specific word, then the whole row should be deleted.
The rest of the macro works fine, I just can't seem to get this part right.

Thanks for the help.

[Zack Barresse]

The rest of the macro works fine ..

I'm worried by this line (before giving any advice). What do you mean exactly? Do you already have some worksheet event code? Is this part of a larger problem/solution?

[Paleo]

When you are deleting rows you MUST care about the direction your deleting loop goes. If it goes from the smaller row to the bigger it WILL incur in error loosing some rows because when you delete a row, the next row goes up and then the loop advances, this makes it to not check a row. The right way is to go from the bigger to the smaller, something like:

For i = Range("A65536").End(xlUp).Row To 1 Step - 1
... code ...
Next

Sure, as Zack said its hard to help with so little information but I would guess this might solve it.

[mdmackillop]

Try the following

Option Compare Text

Sub TestIt()
Dim ToFind As String, c As Range, MyRange As Range
ToFind = "Test"
With Intersect(Range("G:G"), ActiveSheet.UsedRange)
    Set c = .Find(ToFind, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Set MyRange = c.EntireRow
        Do
            Set c = .FindNext(c)
            Set MyRange = Union(MyRange, c.EntireRow)
Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    MyRange.Delete
End With
End Sub

[Anne Troy]

And here's the whole KB article, which may or may not use the same method that MD did. I'm no coder, so I didn't look.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=260

[mdmackillop]

Hi DB
Very similar. I guess I'm needing to familiarise myself with these KB entries a bit better!

[Anne Troy]

Might save you a wee bit of time.

[Paleo]

So outrider,

is it solved?

[outrider]

I'm worried by this line (before giving any advice). What do you mean exactly? Do you already have some worksheet event code? Is this part of a larger problem/solution?

My system takes a large batch of customer orders, sorts them, adds data for parts, production times, materials etc using lookups from other worksheets, then separates the final data into two groups (Assemblies & single items) in a new workbook, this is where the problem starts.
I knew that the deleting process would need to run backwards to ensure no rows would be missed, it just doesn't work properly yet.

[Paleo]

Can you post your macro, at least the part of it thats not working properly?

[mdmackillop]

Hi Outrider,
My suggested code does all the finds before deleting anything, and so does not run backwards. Is there any posibility of hidden characters which would prevent the Find from working?
MD

[outrider]

Adding code supplied by mdmackillop, I have finally got the thing to work.

Thanks for all the help on this problem.