repeat post
Tom
Many thanks, just the job.....
I have remembered something else though....
The sheet is made up of duplicated rows of data, the only difference between
the rows being the addition of any codes in P:T.
Your code deletes any MS only rows of data, but I need it to also delete the
same row without the code. Both rows will have the same ID number in column
A
Is there a way to find MS only codes and then delete any rows with the same
value in column A?
PS
There will be occasions when an MS code doesn't have a duplicate row.
Gareth
{clear as mud}
"Tom Ogilvy" wrote in message
...
For lngRow = lngLastRow To 1 Step -1
' check that only one cell in columns P to T contains a value
if Application.CountA(cells(lngRow,16).Resize(1,4)) = 1 then
For intCounter = 16 To 20 ' check P to T
' check if singe cell with value contains MS and only MS
If Trim(Cells(lngRow, intCounter).Value) = "MS" Then
' a row with MS and only MS in P to T
Rows(lngRow).EntireRow.Delete
Exit For
End If
Next intCounter
End If
Next lngRow
--
Regards,
Tom Ogilvy
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
|