Deleting all cells that contain any cell from another sheet

[SirHart]

Thank you in advance! I certainly appreciate the help with this.

Using Excel 2003, I have a huge list of one word text that are all in the first column. I also have a "Do not use" list that has a smaller list of one word text all i nthe first column. I want to be able to (without using VBA) remove any cells or blank any cell in that column of the huge list if it contains any cells that are in "Do not use" list.

First list:
A1: john
A2: stacy
A3: Katrina
A4: tim
A5: josh
A6: jason

Do Not Use List:
A1: stacy
A2: jim
A3: jason

My lists are MUCH larger. Is there any way to use this Do Not Use list to delete all cells that have the same text in the Large List?

Thank you!

 

[SirHart]

If anyone knows a way to do this using VBA, that could work too! I just don't use it much so you'll have to fill me in on how to do it.

 

[Jonmo1]

Without VBA, it's going to be a process of a few steps..

If the first list (list to delete values from) is on Sheet1,
And the Do Not Use List is on Sheet2

In Sheet1, B1 put
=ISNUMBER(MATCH(A1,Sheet2!$A:$A,0))

Then use AutoFilter to filter Sheet1 Column B for TRUE

Then delete all visible rows.

 

[Rick Rothstein]

Here is a macro that will do what you want...

Sub RemoveNames()
  Dim Cell As Range
  Const DoNotUseSheetName As String = "Do Not Use"
  For Each Cell In Worksheets(DoNotUseSheetName).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    Columns("A").Replace Cell.Value, "", xlWhole
  Next
  On Error Resume Next
  Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlBlanks).Delete xlShiftUp
End Sub

Change the name assigned in the Const statement from "Do Not Use" to the actual name of the worksheet containing the "do not use" list... run this macro from the sheet with your larger list active.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (RemoveNames) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.

 

[SirHart]

Thank you very much for the help. I couldn't get it to work quite yet. I'm not for sure what names to place where in your code?

My spreadsheet's file name is: Names.xls
The large list's sheet is labeled: LargeList
The "Do not use" sheet is labeled: DoNotUse

Sorry about being so slow on this! Thank you so much for your help Rick!

 

[Jonmo1]

OK, so

On LargeList, in B1 put
=ISNUMBER(MATCH(A1,'DoNotUse'!A:A,0))
And fill down to the end of the list in LargeList

Then, on LargeList, use the Autofilter on Column B, and filter for True

Delete all visible rows

 

[Rick Rothstein]

Thank you very much for the help. I couldn't get it to work quite yet. I'm not for sure what names to place where in your code?

My spreadsheet's file name is: Names.xls
The large list's sheet is labeled: LargeList
The "Do not use" sheet is labeled: DoNotUse

Sorry about being so slow on this! Thank you so much for your help Rick!

Your workbook's name is not important as you will be installing the code into it when it is open. The large sheet's name is also not important as it should be the active sheet when the macro is run. So, here is the code with the "do not use" sheet name properly referenced...

Sub RemoveNames()
  Dim Cell As Range
  Const DoNotUseSheetName As String = "DoNotUse"
  For Each Cell In Worksheets(DoNotUseSheetName).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    Columns("A").Replace Cell.Value, "", xlWhole
  Next
  On Error Resume Next
  Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlBlanks).Delete xlShiftUp
End Sub

 

[SirHart]

You both are lifesavers. Thank you so much! They both work beautifully. Sometimes I have people who don't like to use Macros and others who don't mind. So these work amazing. Thank you both! Thank you! Thank you! Thank you!

 

[Jonmo1]

Glad to help, thanks for the feedback.