That would still delete a row that had other codes in addition to MS.
Maybe with the added code:
Private Sub Test()
Dim r As Long
Dim r1 as Range
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1
set r1 = .Range(.Cells(r, "P"), .Cells(r, "T"))
If Application.CountIf(r1,"MS") =1 and Application.CountA(r1) =
1 Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub
If a row with multiple MS codes is to be deleted, then change =1 to 1 for
the first condition.
--
Regards,
Tom Ogilvy
Ron de Bruin wrote in message
...
Try something like this
Private Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1
If Application.CountIf(Range(Cells(r, "P"), Cells(r, "T")),
"MS") 0 Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl
"Gareth" wrote in message
...
Firstly, apologies for asking the same question again but I was afraid
that
I might of been forgotten.
From the question below you will see that I onlty want to delete rows
which
have an MS in the range P:T, the code that Dianne suuplied deletes the
row
if MS exists with any other code.
many thanks
Gareth
--------------------------------
Dianne
Thanks for this but perhaps I didn't ask the question properly:
I only want the row deleted if MS is the only entry in the range, your
code
deletes any row containing MS in the range.
Help........
"Dianne" wrote in message
...
Sub CheckInitials()
Dim lngRow As Long
Dim lngLastRow As Long
Dim intCol As Integer
Dim intCounter As Integer
'Here you will need to use a column
'that will ALWAYS contain a value --
'at least in the very last row
'I have used A in my example
lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
For lngRow = lngLastRow To 1 Step -1
For intCounter = 16 To 20
If Trim(Cells(lngRow, intCounter).Value) = "MS" Then
Rows(lngRow).EntireRow.Delete
Exit For
End If
Next intCounter
Next lngRow
End Sub
--
HTH,
Dianne
In ,
Gareth typed:
I want to check a range (P:T) in every row. These cells will
contain
2 letter codes, if the code 'MS' is found ON ITS OWN then I want to
delete that row. The number of rows on the sheet will vary.
Examples:
MS TG do not delete
MS delete
no codes at all do not delete
BB DD FG TR do not delete
Thanks in advance.
Gareth