Finding and deleting specific rows

[ExcelLLAA]

Hello! I was wondering if there is a formula or command to find and delet specific rows. I want to remove people from an excel email list. On Sheet 1, I have 500 rows with columns for first name, last name, and email. On Sheet 2, I have 30 rows with columns for first name, last name, and email that appear on Sheet 1 but need to be removed. Is there a way to do this without manually searching for each email and then deleting the row? Thanks?

 

[hiker95]

ExcelLLAA,

What version of Excel are you using?

Can we have a screenshot of both worksheets?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[ExcelLLAA]

I am using Excel 2010. I am unable to download the programs on my work computer and the screenshots contain private data. Here's an idea of what it looks like:

Sheet 1:

A B C
1 Jane Smith jsmith@aol.com
2 Bob Jones bjones@aol.com
3 Chaz Johnson cjohnson@hotmail.com

etc. (500 rows)

Sheet 2 has the same format, but with only 30 rows, all of which appear throughout Sheet 1.

 

[ExcelLLAA]

In case that was unclear, first name is in column A, last name in column B, email in column C. Each person has a unique email, so maybe there is a way to find all of the emails that appear in Sheet 1 and Sheet 2 and then automatically delete them (and their corresponding rows) from Sheet 1?

Thanks!

 

[hiker95]

ExcelLLAA,

If I understand you correctly.

Sample search data in Sheet2:

Excel 2007
	A	B	C
1	Jane	Smith	jsmith@aol.com
2	Bob	Jones	bjones@aol.com
3	Chaz	Johnson	cjohnson@hotmail.com
4
Sheet2

Sample raw data in Sheet1:

Excel 2007
	A	B	C
1	ExcelLLAA	ExcelLLAA	ExcelLLAA@aol.com
2	Jane	Smith	jsmith@aol.com
3	hiker95	hiker95	hiker95@aol.com
4	Bob	Jones	bjones@aol.com
5	Chaz	Johnson	cjohnson@hotmail.com
6
Sheet1

After the macro in Sheet1:

Excel 2007
	A	B	C
1	ExcelLLAA	ExcelLLAA	ExcelLLAA@aol.com
2	hiker95	hiker95	hiker95@aol.com
3
4
5
6
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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ExcelLLAA()
' hiker95, 07/31/2013
' http://www.mrexcel.com/forum/excel-questions/717406-finding-deleting-specific-rows.html
Dim c As Range, fr As Long
Application.ScreenUpdating = False
With Sheets("Sheet2")
  For Each c In .Range("C1", .Range("C" & Rows.Count).End(xlUp))
    fr = 0
    On Error Resume Next
    fr = Application.Match(c, Sheets("Sheet1").Columns(3), 0)
    On Error GoTo 0
    If fr > 0 Then
      Sheets("Sheet1").Rows(fr).Delete
    End If
  Next c
End With
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 ExcelLLAA macro.