REMOVE ROW SUBJECT TO STRING VALUE -please review code

[Pharlap]

Hi

Could you review this code and let me know what is wrong with it or rewrite so it will work in the test file.

The code that follows has been put together to try to deleted a row when the word/text "CLASS" appears in a cell of the column , however the code keeps getting a compile error- I have attached a test file. More clearly what the code aims to achieve is this

If in any part of the name string contains word "CLASS" in column G and there is "X" in same row in any columns of I, J, K and/or L - then delete the row so long as there is NO value is present in columns P, R, S, T, and U on the same row - as I want to keep those rows. I have attached the test file but I have added to it some test data for this part of it - these are 6 rows at record numbers 5 to 11 - of these 6 rows in the file are highlighted in blue and one in orange - if the code works well the blue rows will be deleted and retain the orange row,...note in the real file there is no coloured rows I have only added the colour to the test file for demonstation purposes.

Here is the code which needs review

 
Sub test()
    Dim r As Range, txt As String, myRow As Long, x As Long, y As Long, z As Variant
    With Sheets(1)
Again:
        For Each r In .Range("g8", .Range("g" & Rows.Count).End(xlUp))
            If InStr(1, r.Value, "CLASS", 1) > 0 Then
                myRow = r.Row
                x = WorksheetFunction.CountIf(.Range("I" & myRow & ":L" & myRow), "x")
                y = WorksheetFunction.CountBlank(.Range("R" & myRow & ":U" & myRow))
                z = r.Offset(, 9).Value
                If x > 0 And (y <> 4 Or z <> "") Then
                    txt = txt & r.Address(0, 0)
                    If Len(txt) > 245 Then
                        .Range(Mid$(txt, 2)).EntireRow.Delete
                        txt = "": GoTo Again
                    End If
                End If
            End If
        Next
        If Len(txt) Then .Range(Mid$(txt, 2)).EntireRow.Delete
    End With
End Sub

Hope someone will help make this work

Your help will be appreciated

Regards

Pharlap (Tony)

[p45cal]

Try:

Sub test()
Dim r As Range, txt As String, myRow As Long, x As Long, y As Long, z As Variant
With Sheets(1)
    For i = .Range("g" & Rows.Count).End(xlUp).Row To 8 Step -1
        Set r = .Range("g" & i)
        If InStr(1, r.Value, "CLASS", 1) > 0 Then
            x = WorksheetFunction.CountIf(.Range("I" & i & ":L" & i), "x") 'if 0 don't delete
            y = WorksheetFunction.CountBlank(.Range("R" & i & ":U" & i)) 'if <4 don't delete
            z = r.Offset(, 9).Value 'if not empty don't delete
            If x > 0 And y = 4 And z = "" Then r.EntireRow.Delete
        End If
    Next i
End With
End Sub

[Pharlap]

A big thankyou to you p45cal

Thanks heaps it runs great on but the test file and real files

Cheers

Tony (pharlap)