PDA

View Full Version : Solved: Delete row if object NOT found



jaykay100
10-16-2007, 10:03 PM
I am in desperate need of a macro that will search (User Entered) column of my approximate 25,000 line data base and, if it does not find the (User Entered) string in the 2nd row (1st row is headers) then delete that entire row and go to the next row until it finishes the entire data base.

I would like it to say something like;

"Which column do you want to search?"

then

"What character string are you searching for?"

It needs to do a partial string match because most of the time the cell will contain more than just the string I am looking for.

Any help given will be greatly appreciated.

Thanks in advance.

Jim K

Bob Phillips
10-17-2007, 12:38 AM
Public Sub ProcessData()
Dim i As Long
Dim nRow As Long
Dim col As Range
Dim sLookfor As String

With ActiveSheet

Set col = Application.InputBox("Use mouse to select target column", Type:=8)
If Not col Is Nothing Then

sLookfor = InputBox("Look for what string?")
If sLookfor <> "" Then

nRow = .Cells(.Rows.Count, col.Column).End(xlUp).Row + 1
On Error Resume Next
nRow = Application.Match(sLookfor, col, 0)
On Error GoTo 0
If nRow > 2 Then
.Rows("2:" & nRow - 1).Delete
End If
End If
End If
End With

End Sub

jaykay100
10-19-2007, 04:09 PM
BRETTDJ wrote a mcro that I use ALL THE TIME!!!! It can be found under getarticle.php?kb_id=260 (Delete Rows Based On Column Criteria).

Now I need pretty much the exact same thing other than if the string IS FOUND IN THE CELL than the macro skips tht row and if the string is NOT FOUND IN THE CELL then the macro deletes tht row. Obviously it needs to keep doing that until it reached the end of my sheet. What I am trying to say is, I need the same macro that is referenced above but kinda backwards!

Any help give will be gratefully appreciated.

Jim K

jaykay100
10-19-2007, 04:48 PM
Thanks for trying. It almost works! lol.... Maybe you have given me enuff info to figure it out on my own.

Regards,
jim

Zack Barresse
10-19-2007, 09:10 PM
Hi there, welcome to the board!

Since you don't want to check for a single value, but rather every other value, you need to check every value. Does that make sense? So you do need to do an actual loop through everything. Dave (brettdj) was using the Find/FindNext method to not loop through the entire range, thus keeping a speed-efficient routine. You might try something like this ...


Option Explicit

Sub KillRows()

Dim MyRange As Range, i As Long
Dim MatchString As String, SearchColumn As String, ActiveColumn As String
Dim FirstAddress As String, NullCheck As String
Dim AC

'Extract active column as text
AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
ActiveColumn = AC(0)

SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn)

On Error Resume Next
Set MyRange = Columns(SearchColumn)
On Error GoTo 0

'If an invalid range is entered then exit
If MyRange Is Nothing Then Exit Sub

MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value)
If MatchString = "" Then
NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _
"Type Yes to do so, else code will exit", "Caution", "No")
If NullCheck <> "Yes" Then Exit Sub
End If

Application.ScreenUpdating = False
For i = Cells(Rows.Count, SearchColumn).End(xlUp).Row To 1 Step -1
If Cells(i, SearchColumn).Value <> MatchString Then
Rows(i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True

End Sub

HTH

jaykay100
10-20-2007, 08:21 AM
Zack - Thanks for the quick response. Your code works fine but I need a little "Tweak" if you would be so kind.

What happens is..... it works fine if the only thing in the cell is the exact match to my input. In other words, if I tell it to find and keep "Toyota" in column "A" it will delete all the other rows even if they have "Toyota Dodge" or Dodge Toyota" in the column I am working with. The macro will erase all rows unless the cell matches exactly to the word Toyota with no other info in the cell. Can I prevail on your knowledge and kindness one more time?

(Caps or non caps should be treated the same).

Thanks again!
Jim K

lucas
10-20-2007, 08:46 AM
Hi Jim,
I have merged these two threads as they are asking exactly the same question. It doesn't help us to help you if you do this. It only makes it confusing and hard to help you. It might even make some folks not want to help you at all....

lucas
10-20-2007, 09:49 AM
Option Explicit
Option Compare Text
Public Sub SaveRowsWithString()
Dim i As Long
Dim iLastRow As Long
Dim col As Range
Dim sLookfor As String
With ActiveSheet
Set col = Application.InputBox("Use mouse to select target column", Type:=8)
If Not col Is Nothing Then
sLookfor = InputBox("Look for what string?")
If sLookfor <> "" Then
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Not .Cells(i, col.Column).Value Like sLookfor Then
Rows(i).Delete
End If
Next i
End If
End If
End With
End Sub

lucas
10-20-2007, 11:56 AM
Hi Jim,
Further testing shows that my previous code does not work on partial strings....this code works on partial strings but you have to code in the column and the string....may not be convenient and maybe someone can help you get the Like operator to work with variables(I was not able in the short time I tried)
But if you are in a bind...this will work for the short run:
Option Compare Text
Public Sub SaveRowsWithLabor()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet
'change next line to determine which column to find last row in.
' iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If Not .Cells(i, TEST_COLUMN).Value Like "*Labor*" Then
Rows(i).Delete
End If
Next i

End With

johnske
10-20-2007, 02:41 PM
for variables,If Not .Cells(i, TEST_COLUMN).Value Like "*" & sLookfor & "*" Then

lucas
10-20-2007, 02:51 PM
Thanks...That works great John, I tried a brazillion ways as GW would say.

Option Explicit
Option Compare Text
Public Sub SaveRowsWithString()
Dim i As Long
Dim iLastRow As Long
Dim col As Range
Dim sLookfor As String
With ActiveSheet
Set col = Application.InputBox("Use mouse to select target column", Type:=8)
If Not col Is Nothing Then
sLookfor = InputBox("Look for what string?")
If sLookfor <> "" Then
'decide which column to look in for the last row
' iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iLastRow = .Cells(.Rows.Count, col.Column).End(xlUp).Row
For i = iLastRow To 2 Step -1
If Not .Cells(i, col.Column).Value Like "*" & sLookfor & "*" Then
Rows(i).Delete
End If
Next i
End If
End If
End With
End Sub

jaykay100
10-20-2007, 02:52 PM
Sorry.... I just am kinda in a panic to get this macro running. Running out of time. I hate being macro stupid!..... Won't do it again.
Jim K

jaykay100
10-20-2007, 04:47 PM
You guys have saved my bacon! I tested the macro on my test file and it worked just fine.

Now to put it to work on my big files!

Thanks, Thanks, Thanks!!!!!!

Jim K

jaykay100
10-21-2007, 06:22 AM
On small files this code is very fast but, the larger the file the slower it runs which, of course is normal. However, it seem that the slow-down is way out of porportion to the size of the file. That is, if it takes 10 seconds to run a 1,000 item file, instead of the expected 20 seconds to run a 2,000 item file it really takes like 40 seconds. Is that normal or is it my computer (which is usually pretty darn fast).

Regards,
Jim K

lucas
10-21-2007, 07:06 AM
It could probably be quite a bit faster if you didn't need...to look for part....in other words if you would type the entire item you are looking for as the string instead of only part of it....see where I'm going with this....

jaykay100
10-21-2007, 09:52 AM
It could probably be quite a bit faster if you didn't need...to look for part....in other words if you would type the entire item you are looking for as the string instead of only part of it....see where I'm going with this....

Once again let me state that I am really happy with the macro. Just my inquiring mind doing its usual thing.

Actually I always type the entire string like Toyota or Dodge or whatever. But, when the macro looks at the cell I need it to find "Toyota" even if Toyota is contained in other strings such as "Ford, Dodge, Toyota, Kia, Honda" and it does that so I am happy.

I was just curious why the speed decreases logarithmically as the size of the file goes up. In my programming ignorance I would expect it to be a normal progression such as twice as many rows would take twice as long.

I do consulting and other things for car dealers all over the country so I have a data base with every new car dealer listed (Roughly 25k names). If I want to mail or call or fax, lets say a Toyota dealer, I have the macro look for names that contain the string Toyota. However a lot of dealerships have multiple franchises so the Name column may have a entry like Joe Blow's Toyota, GMC, Dodge, Honda and Kia dealership. Hence the need for the Macro to keep that row but delete all the other rows that don't have the string Toyota in them.

I would think that a number of people other than myself would have need of a macro like this. Too bad most of them won't know where to go to get it.

I really, really appreciate your help. It's a real life saver.

Regards,
Jim

lucas
10-21-2007, 09:57 AM
Hi Jim,
I'm just saying that the requirement to parse for partial strings is probably what is slowing this thing down for you. If it's a serious problem maybe you could add a column with just an association such as Toyota and that column could be searched much quicker......You could use a filter instead of parsing each cell.....

jaykay100
10-21-2007, 10:34 AM
I can certainly live with it! Not a problem, was just curious. Thanks again!:bow: :bow:

johnske
10-21-2007, 02:05 PM
A couple of things... As Steve has said, this is not the fastest way to do things and using (for example) the Find function would be very much faster. Never-the-less there's a couple of things that will speed this up, one is to put ScreenUpdating equal to false, another that will help a great deal is to not delete each row one at a time within the loop, but instead to add the address of the found string to a string variable and then delete all the rows in one hit after the loop has ended.