check range for certain value

  • Thread starter Thread starter Gareth
  • Start date Start date
G

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
 
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
 
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........
 
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
 
Back
Top