Macro To Search Multiple Values and Delete Rows

[pfauntle]

Hello all-
I am by no means an Excel master, and could really use some help. I tried searching the forums, but couldn't find anything that directly answered this question.

I have a database of over 55,000 rows and am trying to find a macro to search for a number of different values and then remove any row that contains that value. Basically, in column A there is a unique node ID number and I want to be able to search by Node ID and remove the row containing that id. I only plan to search by this ID number and there shouldn't be any duplicates because each row has it's own identifier.

My other question is if there is a macro that can be designed for this, what is the max number of ID numbers I can search for? I have roughly 2500 different numbers that need to be pulled so I feel like the sheer amount may make things difficult.

Here are some examples of what the Node ID Numbers look like: 985499, 338270, 891220, 610684.

This was also the closest thing I could find in my scouring for an answer, though I couldn't exactly get it to work...
http://excel.bigresource.com/Track/excel-lOT4W6Xp/

Thanks in advance for your time and help. If you need more info or something, just ask. Thank you!

-Pat
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:middle; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {text-align:center; vertical-align:bottom; white-space:normal;} --> </style> <table style="border-collapse: collapse; width: 167px; height: 17px;" border="0" cellpadding="0" cellspacing="0"> <col style="mso-width-source:userset;mso-width-alt:5333;width:125pt" width="125"> <tbody><tr style="height:12.0pt" height="12"> <td class="xl63" style="height:12.0pt;width:125pt" height="12" width="125">
</td> </tr> </tbody></table>

 

[ChrisOswald]

I'm guessing that with 2500 id's you want to exclude you probably have the excluded id's on a list somewhere.

If you do have them in a list, the quickest/easiest way is to use a helper column to set up a filter that you can delete from.

Example: here's your full table:

Excel Workbook
	A	B	C
1	ID	Fld1	Fld2
2	1	aa	abc
3	2	bb	def
4	3	cc	ghi
5	4	dd	jkl
6	5	ee	mno
7	6	ff	pqr
8	7	gg	stu
9	8	hh	vwx
10	9	ii	yza
11	10	jj	bcd
Sheet2

#VALUE!

Excel 2007

Then, on Sheet2 above, you'd enter in d2 this formula:

=if(countif(Sheet3!A:A,A2)>0,"Kill","")

(obligatory warning before next step: Make sure you keep a copy of your original datasheet somewhere)

Put on an autofilter on sheet2 row1, show only the items marked "Kill", and delete them off and then delete the helper column.

With 55K items on the list, you'll want to do a copy/paste special - values back on your helper column and then sort for the items labeled 'kill': otherwise it could take a while to delete stuff out.


If it does turn out that you need a macro (for example, you need to do this task 20 times a day), there is really no practical limit to the number of exclusionary items, provided, of course, you have them in a list somewhere -- you should be able to pretty much record the above method into a macro.

If you don't have a list of id's to exclude... um, better you than me?

 

[mirabeau]

Hi Pat,

Good luck with your investigations!

You may like to try this VBA macro.

List your node numbers (for the exclusions) in columnA, sheet2. Don't use a header. There's no limit to how many of these you want to use. Nor does it matter if you have any them duplicated.

With data on another sheet, run the macro.

Your node numbers should be positive numbers as you specified, otherwise this macro may need modification.

Sub deleterows()
Dim b() As Boolean, q(), a As Range, e
Dim n&, m&, mn&, mx&, i&, k&
Set a = Cells(1).CurrentRegion
n = a.Rows.Count: m = a.Columns.Count
ReDim q(1 To n, 1 To 1)
u = Range("A2:A" & n)
mn = Application.Min(u)
mx = Application.Max(u)
ReDim b(mn To mx)
On Error Resume Next
For Each e In Sheets("sheet2").Cells.Resize(, 1)(1).CurrentRegion.Value
    b(e) = True
Next
On Error GoTo 0
For i = 2 To n - 1
    If b(u(i, 1)) = True Then q(i, 1) = 1
Next i
Cells(2, m + 1).Resize(n - 1, 1) = q
a.Resize(, m + 1).Sort Cells(1, m + 1), 1, Header:=xlYes
k = Application.CountA(Cells(2, m + 1).Resize(n - 1))
If k > 0 Then Range("A2", Cells(k + 1, m + 1)).Delete xlUp
End Sub

 

[pfauntle]

Thank you both for your timely help help! Unfortunately, I can't quite get either of these to work....

I do have a list of the id's i want to exclude listed, so that's not a problem. Maybe this is all just over my head? haha

mirabeau- I'm getting an error when I run the macro. Says Run-time error '13', type mismatch. And when you go to debug its pointing to: If b(u(i, 1)) = True Then as the error.

Chris- For the filter, I tried running the if statement, but can't get anything to be marked 'kill'. Clearly I must be doing something wrong

Would these both only work in excel 2007? I unfortunately am running 2011 on a mac ( I know the capabilities of excel are limited on a mac, but its all I got at this point) so I know I can run macros. I guess I should've specified that before I started.

Thanks again for all your help!

 

[mirabeau]

Pat,

There is an error in that code, please modify as indicated in red, and retry.

However, that error would only cause one line to be possibly not deleted when it should be. But it should not cause the error you indicated.

Your type mismatch error would most likely be due to the u(i,1) (i.e. one or more of your set of node numbers) not being a positive integer.

You could probably bypass it by deleting the "on error goto 0" statement, but for serious work the error should really be tracked down.

Could you do the following:?
In a new workbook, run the following testdata code. It will put some obvious entries on Sheet1 and on sheet2 columnA will put the first 2 Node ID's.

Examine these data and see if they give a fair representation of your general problem.

Then run the modified code code as re-posted below and see if it produces, without error, the result you'd want.

It all works good with me. I can't get an error.

Then , if you're happy, you can change the n=10 to n=55000, and crit=2 to crit=2500, to better correspond in size with problem in your opening post.

Sub testsdata()
Dim n&, m&, crit&, forsht2
Sheets("sheet1").Activate
ActiveSheet.UsedRange.ClearContents
n = 10: crit = 2
m = 5
With Cells.Resize(n, m)
    .Cells = "=char(int(rand()*12+97))"
    .Resize(, 1) = "=int(rand()*9*10^5)+10^5"
    .Resize(1) = "=""Col"" & char(column()+64)"
    .Value = .Value
    .Columns.AutoFit
    forsht2 = .Resize(crit).Offset(1)
End With
Cells(1) = "Node ID"
With Sheets("sheet2").Cells.Resize(, 1)
    .ClearContents
    .Resize(crit) = forsht2
End With
End Sub

Sub deleterows() 'as modified
Dim b() As Boolean, q(), a As Range, e
Dim n&, m&, mn&, mx&, i&, k&
Set a = Cells(1).CurrentRegion
n = a.Rows.Count: m = a.Columns.Count
ReDim q(1 To n, 1 To 1)
u = Range("A2:A" & n)
mn = Application.Min(u)
mx = Application.Max(u)
ReDim b(mn To mx)
On Error Resume Next
For Each e In Sheets("sheet2").Cells.Resize(, 1)(1).CurrentRegion.Value
    b(e) = True
Next
On Error GoTo 0
For i = 1 To n - 1 'changed
    If b(u(i, 1)) = True Then q(i, 1) = 1
Next i
Cells(2, m + 1).Resize(n - 1, 1) = q
a.Resize(, m + 1).Sort Cells(1, m + 1), 1, Header:=xlYes
k = Application.CountA(Cells(2, m + 1).Resize(n - 1))
If k > 0 Then Range("A2", Cells(k + 1, m + 1)).Delete xlUp
End Sub

 

[ChrisOswald]

Pat: is it possible that you have some spaces and/or non-printing characters in one of the lists? that'd cause countif to fail, and it might be the cause of the run time error you're getting with mirabeau's code.

 

[pfauntle]

Thank you so much to both of you for your help.

I finally got it to work, and it works like a charm!

My list of 2500 or so ID's was fine, it was the actual database that was the problem. There were a handful of ID #'s that had commas and quotations in the field with the node number. Once I sorted those out, the macro ran just fine. Thanks again! I'm sure I'll be back soon with more questions/problems.

 

[pfauntle]

Alright guys. So I thought this was all taken care of (because it was working in my past workbooks) but we exported a new copy from our database and now I'm getting the same error I was earlier (run time error 13) but now its pointing to this line mn = Application.Min(u) as the error.

I know that last time it was the non-numeric characters that were causing the error but I haven't been able to find any problems in the data (at least that are as obvious ie the commas, quotes, etc. from last time). Last time I just did a sort and I was able to find the cells that had the non-numeric characters real easy, but that's not working this time... Is there anything I can run to try and see what's causing this problem? I don't think I can upload the database here, but I'm just really lost as to what would be causing this problem because it all looks good to me at this point.

As always, thanks for your help!

 

[mirabeau]

There's 2 reasons that occur to me why the Min function might not work.

If there's non-numeric data in the specified range, or if the number of items in the relevant range is (from memory) > 65536

Direct importing of the built-in Excel functions to VBA codes can often be a bit iffy and may need some care.

If the range length is > 65536 it's easy enough to do an alternative. If you've got some non-numeric data (say Chr(160) which often causes a problem) then that procedure won't work anyway.

 

[pfauntle]

All fixed! Had around 110,000 rows, split them into two different sheets and it worked just fine. Feel pretty dumb, but it happens I guess....

Thanks again for your help!