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
|