check range for certain value

[Gareth]

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

 

[Dianne]

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

 

[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]

Ah. I see. Try this instead:

Sub CheckInitials()
Dim lngRow As Long
Dim lngLastRow As Long
Dim intCounter As Integer
Dim strConcatenate As String

'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
strConcatenate = strConcatenate & _
Trim(Cells(lngRow, intCounter).Value)
Next intCounter
If strConcatenate = "MS" Then
Rows(lngRow).EntireRow.Delete
End If
strConcatenate = ""
Next lngRow
End Sub

 

[Tom Ogilvy]

Just for information, Gareth started a new thread on this.