VBA: Match Against a Blacklist and Delete Row??

[ONEILR2192]

Hello,

I'm trying to figure out a VBA solution to do the following:

If a cell in Sheet1 (my main sheet) range A7:A2007 is equal to any cell in Sheet2 (my blacklist) range A:A; Delete that entire row in Sheet1. (Both of these sheets are in the same workbook)

Because I'm already using some unrelated filters in the ranges specified I can't use a filter solution, I'm thinking it has to be a loop. I can't for the life of me figure out the language to accomplish this task. Hopefully some of the wizards around here can figure it out! PLEASE HELP!

Thanks!

 

[hiker95]

ONEILR2192,

Welcome to the MrExcel forum.


You did not show us what the raw data in Sheet1 and Sheet2 looked like.


Sample raw data:

Excel 2007
	A
1	15
2	17
3	99
4	100
5	222
6	9
7	7
8
Sheet2

Excel 2007
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	9
9	10
10	11
11	15
12	17
13	99
14	15
15	17
16	99
17	15
18	17
19	99
20	100
21	222
22
Sheet1

After the macro in Sheet1:

Excel 2007
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	10
8	11
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub IfinBlacklistDelete()
' hiker95, 02/16/2013
' http://www.mrexcel.com/forum/excel-questions/686091-visual-basic-applications-match-against-blacklist-delete-row.html
Dim s1 As Variant, s2 As Variant
Dim i As Long, lr As Long, fr As Long
Application.ScreenUpdating = False
s1 = Sheets("Sheet1").Range("A1:A2007")
s2 = Sheets("Sheet2").Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
For i = UBound(s1, 1) To LBound(s1, 1) + 6 Step -1
  fr = 0
  On Error Resume Next
  fr = Application.Match(s1(i, 1), s2, 0)
  On Error GoTo 0
  If fr > 0 Then Sheets("Sheet1").Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the IfinBlacklistDelete macro.

 

[ONEILR2192]

hiker95: Your code worked FLAWLESSLY!

Thank you so much!

 

[hiker95]

ONEILR2192,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Adomynous]

Hello all,

I came across the brilliant code of hiker95 whilst trying to find something very similar but have been unsuccessful so far and was wondering if anyone could help me.

Just like ONEILR2192, I would like to use a Blacklist in order to filter through a sheet in excel. The only variation here is that my data looks like this:

Sheet1: Single column with unique text in a cell (contains company names, so letter and numbers).
Sheet2 (Blacklist sheet): Single column with unique text in a cell that is to be compared with Sheet 1 and if a match is found, highlight the matching string.

For the record, before posting here, I tried re-engineering hiker95's code to meet my conditions, but after several hours of VBA tutorials (5+), a broken keyboard and no programming knowledge I figured I might as well ask some real pro's.

Thanks in advance for the time, help and effort.

Kind Regards,

A.

 

[hiker95]

Adomynous,

Welcome to the MrExcel forum.

So that we can get it right on the first try, can we see your actual raw data worksheets, and, what the results should look like?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Adomynous]

Hiker95,

Thanks for your quick reply!

https://www.dropbox.com/s/afe9zc81o8awlp1/Blacklist test.xlsm?dl=0

This is how the file looks like. I'll try and explain this to the best of my ability:

I have to filter through a list of company names. In order to not have to remember if I have received the name or not, I'd like for the name to be "blacklisted" once I have received it.

So in the file you can see in Sheet1 a list of company names. The blacklist (Sheet 2) is based on a list of company names I already posses and will become bigger the more names I receive. This is my desired workflow:
1) Receive list of company names.
2) Paste them in Sheet1, run the macro, and based on the blacklist of Sheet2, have any known company become highlighted in yellow.
3) Once the highlight is done, I will then copy the current selection in Sheet1 and add it to Sheet2.
4) I will then remove duplicates from said blacklist (sheet 2) in order to have a unique blacklist.

Step 3 and 4 do not need to be coded, I would like to do these things myself.

What I tried doing with the knowledge I have is simply turn sheet1 and sheet 2 into arrays, then use the ubound and lbound to loop in these arrays, combined with application.match
The part I got stuck on is how to make it match a unique string. But as I mentioned before, my knowledge is very limited

I hope I got to explain this well enough. Please let me know if you need any additional information.

Thanks for your time,

A.

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub HighlightBlackListedCells()
  Dim R As Long, BlackList As Variant
  BlackList = Sheets("Sheet2").Range("A1").CurrentRegion
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbYellow
  For R = 1 To UBound(BlackList)
    Sheets("Sheet1").Columns("A").Replace BlackList(R, 1), "", searchformat:=False, ReplaceFormat:=True
  Next
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[Adomynous]

Hi Rick,

Thanks a bunch! I have tested it with a few files and it works, however it only works with smaller files (20-30 entries in sheet1 and the similar amount in sheet 2).
The file I am currently trying to run the macro through has 300+ entries in sheet1 and around 2500 in sheet2.

I have the error: Runtime error "13": Type mismatch
When I then open the debugger, this is the line of code that is highlighted: Sheets("Sheet1").Columns("A").Replace BlackList(R, 1), "", searchformat:=False, ReplaceFormat:=True

 

[hiker95]

Adomynous,

In reference to your link in your link in reply #7:

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please remove all macros, and, then rename the workbook using the xlsx file extension, and, then repost on dropbox.

Then, in your next reply, it would help us if you posted all of your macro code using code tags.

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]