PDA

View Full Version : REMOVE ROW SUBJECT TO STRING VALUE -please review code



Pharlap
08-05-2007, 01:59 AM
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
08-05-2007, 05:30 AM
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
08-05-2007, 05:32 PM
A big thankyou to you p45cal

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

Cheers

Tony (pharlap)