If I've understood your query, you want to delete all rows on which a string
occurs, in any case/spelling. Try this:

Sub findit()
' Delete rows on which the string (NOT substring) locate occurs, in any
case, on activesheet
locate = "rules"
For Each Row In ActiveSheet.UsedRange.Rows
For Each cell In Row.Columns
If 0 = StrComp(locate, cell.Value, vbTextCompare) Then
ActiveSheet.Rows(Row.Row).EntireRow.Delete
End If
Next
Next
End Sub


"Gordon" wrote:

Hi...

Each monday I get a spreadsheet that's 10,000 lines deep
and 100 across. My task is to locate certain values
within any cell (within any column or any row) and then
delete the row in which the value was found. Typically I
have about 300 values to find and it takles me around 4
hours - I've reached breaking point!

Ideally I'd like to click on a macro button that will
launch an input box that will allow me to for example
enter the name Jones. When I click OK on the input box I
want a macro to run through the entire sheet locating
every occourance Jones and deleting every row in which
the value Jones appear (lifting cells upwards of course)

The code below is as far as I've got (thanks from Nigel)
but whilst this code cycles nicely it doesn't actually
locate the values or text striongs that I need to be
found and then removed, it ignores all the Jones values
and then tells me that no JOnes values exists and that no
rows have been removed...Why? Can anyone help?

Public Sub remove()
Worksheets("Sheet1").Activate
Dim lastrow As Long
Dim lastcol As Long
Dim sString As String
sString = InputBox("Delete Row(s) were cell has this
value.")
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count
Application.ScreenUpdating = False
Dim ir As Long, ic As Long, rd As Long
For ir = lastrow To 1 Step -1
For ic = lastcol To 1 Step -1
If Cells(ir, ic).Value = sString Then
Rows(ir).Delete shift:=xlUp
rd = rd + 1
End If
Next ic
Next ir
Application.ScreenUpdating = True
MsgBox "Deleted: " & rd & " rows"
End Sub

Thanks

Gordon