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!

 

[Rick Rothstein]

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

My code has nothing in it that should limit it to small files like you are reporting. Any chance you can post a copy of the workbook where my code is not working for you? If so, please use DropBox to post it.

 

[Adomynous]

Hiker95,

Thanks for the quick reply again.

I have uploaded a file which is outdated to me, but contains the exact names of companies and such, so this isn't a dummy file There is no sensitive information in this excel.

https://www.dropbox.com/s/gw0aijkej9rsaxg/Blacklist.xlsx?dl=0

This is the code I used, submitted by Rick

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

This is the specific line where the debugger highlighted the code

Sheets("Sheet1").Columns("A").Replace BlackList(R, 1), "", searchformat:=False, ReplaceFormat:=True

 

[Rick Rothstein]

I have uploaded a file which is outdated to me, but contains the exact names of companies and such, so this isn't a dummy file There is no sensitive information in this excel.

https://www.dropbox.com/s/gw0aijkej9rsaxg/Blacklist.xlsx?dl=0

Sheet2 contains your blacklist, correct? Please explain the contents of cell A38 on Sheet2 please... it looks like a comma delimited list of companies rather than a single company name like your original post leads us to believe is what your blacklist is constructed of. Is that cell a mistake or do we have to account for multiple names in a single cell?

 

[Adomynous]

Hi Rick,

First off, thanks for the help so far. Regarding the cell A38 on Sheet2, this is correct, sometimes there are combinations of companies that are put in a single cell. If this makes it much more complicated I'll just use text-to-columns and blacklist based on the first company name.

 

[Rick Rothstein]

Hi Rick,

First off, thanks for the help so far. Regarding the cell A38 on Sheet2, this is correct, sometimes there are combinations of companies that are put in a single cell. If this makes it much more complicated I'll just use text-to-columns and blacklist based on the first company name.

No, a comma delimited list in a cell is not a problem, I just had to know it was a possibility so that I could construct the code to handle it... your original post gave no hint that comma delimited lists could exist within a single cell (you must keep in mind when posting a question here that we volunteers here know absolutely nothing about your data except what you tell us). See if this macro works for you...

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

 

[Adomynous]

I have tried it with smaller and larger files and it works consistently. Thank you Rick Rothstein and Hiker95 for your time and effort, it is greatly appreciated.
I will try to keep in mind that the "obvious" isn't at all obvious if others don't know/see what I know/see.

Thanks again gentlemen.

Kind regards,

A.

 

[hiker95]

Thank you Rick Rothstein and Hiker95 for your time and effort, it is greatly appreciated.

Adomynous,

You are very welcome.

One last try:

Can you supply another workbook with the raw data in Sheet1, and, Sheet2, and, in Sheet11 what sheet1 results should be, and, in Sheet22, what the results should be in Sheet2?

 

[Adomynous]

Sure thing!

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

Thanks again!

 

[hiker95]

Sure thing!

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

Thanks again!

Adomynous,

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.

 

[Adomynous]

Adomynous,

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.

Whoops,

https://www.dropbox.com/s/eh3h29froi02lg0/Blacklist example.xlsx?dl=0

Wrong list, sorry!