check range for certain value
 

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

check range for certain value
 

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

check range for certain value
 

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

check range for certain value
 

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

--
HTH,
Dianne

In ,
Gareth typed:
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

check range for certain value
 

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

--
Regards,
Tom Ogilvy

"Dianne" wrote in message
...
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

--
HTH,
Dianne

In ,
Gareth typed:
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