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

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

Gordon,
I'm no expert, but I can suggest you enter the extra line of code bvelow to
see which range you are actually looping through. You'll see that depending
on where your UsedRange is located, you may be missing some of your data.
Also, in your example, you say that you are searching for "Jones", but not
finding "JOnes". If this is not a typo and you are looking for that text in
any case, use something like:
If LCase(Cells(ir, ic).Value) = LCase(sString) Then

In all though, you may find the Find method more effecient to looping
through potentially 1,000,000 cells.

NickHK

"Gordon" wrote in message
...
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
Cells(ir, ic).Select 'Extra line
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

Hi Gordon

I'd use Excel's own search functionality instead of the loop. It should run
far faster. Try this:

Sub KillRows()
Dim Cel As Range
Dim sFind As String
Dim L As Long
Do
sFind = InputBox("Delete rows with:", _
L & " rows deleted so far")
If sFind = "" Then Exit Do
Do
Set Cel = Nothing
On Error Resume Next
Set Cel = Cells.Find(What:=sFind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If Cel Is Nothing Then Exit Do
Cel.EntireRow.Delete
L = L + 1
Loop Until Cel Is Nothing
Loop Until sFind = ""
MsgBox L & " rows deleted"
End Sub

Replace xlWhole with xlPart if you want cells with "Joe Jones" deleted in a
"Jones" search.

HTH. Best wishes Harald

"Gordon" skrev i melding
...
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

"CoRrRan" skrev i melding
...
"Tom Ogilvy" wrote in
:

But I have suggested in the other thread that a different approach
(Harald Staff's) should be better.

Lesson for today: don't continue with code that is somewhat flawed or
could be better...

Thanks CoRrRan. Wonder why Gordon won't touch it.

Best wishes Harald