Tweaking Code - Experts Only!

[Gordon]

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

 

[AA2e72E]

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

 

[CoRrRan]

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 think your main problem is that the "search string" and
"found string" have to be case consistent. I have changed your code a
bit, have a look if it performs what you want:

*********************************************************************
Option Explicit

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).Activate
If UCase(Cells(ir, ic).Value) = UCase(sString) Then
' ^^^^^^-------------------^---^^^^^^-------^-----
' Added this to make found string & search string uppercase
Rows(ir).Delete shift:=xlUp
' Added to proceed to next line (speed increase):
ir = ir - 1
' Added to proceed to last column + 1 (speed increase):
ic = lastcol + 1
rd = rd + 1
End If
Next ic
Next ir

Application.ScreenUpdating = True

MsgBox "Deleted: " & rd & " rows"

End Sub
*********************************************************************

As you can see, I have added the UCase-functions in the marked line.
This makes sure that the "to be found"-string and the "found"-string
are case-consistent.

If you do not want this, then your code is adequate for your
purposes. I ran the code on a spreadsheet with random data, and it
works like a charm.

HTH,
CoRrRan

 

[NickHK]

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

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

 

[Harald Staff]

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

 

[Tom Ogilvy]

Your changes will cause the macro to miss checking cells which it is doing
already, but just exacerbates the problem.

 

[CoRrRan]

Your changes will cause the macro to miss checking cells which it
is doing already, but just exacerbates the problem.

It might look like it, but I have checked the code, and these additions
make sure that the code starts to check the row ABOVE the row that WAS
deleted. Therefore, it isn't missing cells, I believe.

BUT I did find a problem that when the "to be found" value is in the
first row, and this row gets deleted, that the macro will cause an
error.

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...

CoRrRan

 

[Harald Staff]

news:#[email protected]:

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