Deleting text in a column

M

mikemcglone

Im trying to create a function that will delete the entire row if a
cell contains the text ***SCRATCHED***.


Function REMOVESCRATCHINGS()

Init = Range("C1:C200")

For COUNTER = 1 To 200

If Init = "***SCRATCHED***" Then
Selection.Delete shift:=xlUp

End If

Next

End Function

I get a type mismatch error message

Thanks for any help.
 
M

Mike H

Hi,

It's no longer a function it's a sub so try this

Sub REMOVESCRATCHINGS()
For x = 200 To 1 Step -1
If Cells(x, 3).Value = "***SCRATCHED***" Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Mike
 
M

mikemcglone

Hi,

It's no longer a function it's a sub so try this

Sub REMOVESCRATCHINGS()
For x = 200 To 1 Step -1
    If Cells(x, 3).Value = "***SCRATCHED***" Then
        Rows(x).EntireRow.Delete
    End If
Next
End Sub

Mike












- Show quoted text -
Click to expand...

Mike ..I'll have to play around with your sub - nothing happens when I
run it.. at least there's no error messages. Thanks
 
M

mikemcglone

Mike ..I'll have to play around with your sub - nothing happens when I
run it.. at least there's no error messages. Thanks- Hide quoted text -

- Show quoted text -
Click to expand...

Mike...The sub works fine for numbers but I cant make it work for
text
 
M

Mike H

Hi Mike,

2 versions. The top one works for text and the second for numbers. Note that
the asterix in the first aren't wildcards, if thats what you want post back.
As written it will delet rows where C contains ***SCRATCHED*** and it's case
sensitive so it wont delete "***SCRATCHeD***"



Sub REMOVESCRATCHINGS()
For x = 200 To 1 Step -1
If Cells(x, 3).Value = "***SCRATCHED***" Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Sub REMOVESCRATCHINGS1()
For x = 200 To 1 Step -1
If Cells(x, 3).Value = 99 Then
Rows(x).EntireRow.Delete
End If
Next
End Sub


Mike
 
M

Mike H

Steve if you want wilcards for text and something that isn't case sensitive
use this

If UCase(Cells(x, 3).Value) Like "*SCRATCHED*" Then



Mike
 
M

mikemcglone

Steve if you want wilcards for text and something that isn't case sensitive
use this

If UCase(Cells(x, 3).Value) Like "*SCRATCHED*" Then

Mike
Click to expand...

Mike H .. Works fine with this line replacing the original. I dont
understand why your original sub didnt work. Many thanks Mike M
 
Top