Remove rows that contain full or partial email addresses

[Serafin54]

Hello,

I have a report which contains email addresses in column F most are valid but some are not. I am needing to delete any rows containing an invalid email address. Below is a dummy file, same set up but data changed for privacy. Yellow highlighted cells are ones with issues.

PROD1 CASS_RESULTS.xlsx
	A	B	C	D	E	F
1	Address	item	department	code	EE LAST NAME	Employee Home Email
2	64 Central St.	checkbook	22954	JX8939	WILSON	cyrus@hotmail.com
3	65 W. Military Dr.	doll	13910	JF1074	KRANTZ	dmouse@AGMAIL.COM
4	66 North Nicolls Ave.	bottle	7828	JW8594	MISAL	andrei@me.com
5	7047 Poor House Drive	bananas	8802	GQ6511	SANCHEZ MARTINEZ	jbarta@me.com
6	8652 Locust Ave.	pencil	91224	JW5310	GARNIER ROVIRA	report@hotmail.com
7	4 Selby St.	apple	25241	D59732	CASTANEDA	jmorris@mac.com
8	53 Sulphur Springs Street	nail clippers	24690	KC1501	ROSADO	karasik@GMAIL..COM
9	518 Cobblestone Drive	money	18835	GQ3054	MOORE	gumpish@msn.com
10	7761 Old York Street	sailboat	16892	M44288	BATTCOCK	natepuri@yahoo.com
11	98 Oakland Rd.	sticky note	24934	S76416	PARRILLA BRUNO	kalpol@yahoo.ca
12	9689 Homestead Dr.	buckle	101012	JZ8577	KOONCE	ideguy@hotmail.com
13	35B 53rd Street	chapter book	630157	DD9192	Demeusy	camenisch@mac.com
14	9468 Victoria Drive	couch	640196	KL6703	CORRALES	jigsaw@yahoo.com
15	3 Warren St.	photo album	18041	T26488	BUTTERFIELD	michiel@msn.com
16	8468 Vine Rd.	needle	20310	GZ9574	BEILENSON	dprice@gmail.com
17	18 Lookout Ave.	wallet	701064	GH5648	CHARPENTIER	NOEMAIL@GMAIL.COM
18	7302 Stillwater Ave.	clay pot	24934	GG1666	SMITH	None
testfile

Currently, I open my report and also an excel file I created named "Invalid Emails" with two columns. Column A is the full address that may contain items such as "NOEMAIL@GMAIL.COM", "1234@567.com", or even "@gmail.com" and 36 entries . And column C that has only the extensions which may be "AGMAIL.COM", "GMAIL.CON", etc with 62 entries. Below is part of the list. Yellow Cells relate to the yellow cells on the report example from above.

PROD1 CASS_RESULTS.xlsx
	A	B	C
1	First Lookup		Second Lookup
2	N/A		.com
3	NA		AGMAIL.COM
4	NL		aol.om
5	None		att.netde
6	@g.com		GAMAIL.COM
7	@gmail.com		GAMIAL.COM
8	0@0.CM		GAMIAL.COM
9	0@0.COM		GAMIL.COM
10	1234@567.com		GIMAIL.COM
11	a@a.com		GMAI.COM
12	ABC@ABC.COM		GMAIIL.COM
13	ABC@NOEMAIL.COM		GMAIL..COM
14	ABC@xx.COM		GMAIL.CO
15	NA@NA.COM		GMAIL.COJM
16	NOEMAIL@GAMIL.COM		G-MAIL.COM
17	NOEMAIL@GMAIL.COM		gmail.com.
18	NOEMAIL@HOTMAIL.COM		gmail.com.
19	NOEMAIL@NOEMAIL.COM		GMAIL.COMG
20	NOEMAIL@SHORTYS.COM		GMAIL.COMM
Sheet1

I begin by entering a vlookup in column G2 of my report using F2 of my report to reference column A of the Invalid Emails file and filling down. I then filter F to exclude "#N/A" and delete all rows that appear.

I unfilter and copy/paste all remaining emails in F into G, find "*@" and replace with "" to get the extension of the emails and enter another vlookup into H2 on my report to column C of the Invalid Emails file and filling down. I then filter K to exclude "#N/A" and delete all rows that appear. Unfilter and delete columns G and H.

This process isn't back breaking by any means but am curious if there is a way to accomplish this through VBA or not.

 

[Flaiban]

Try this code :

Sub DeleteYellow()
    Dim lr As Long, i As Long
    Application.ScreenUpdating = False
    
    lr = Range("F" & Rows.Count).End(xlUp).Row
    
    For i = lr To 2 Step -1
        
        If Cells(i, "F").DisplayFormat.Interior.ColorIndex = 6 Then Rows(i).Delete
 
    Next
    
    Application.ScreenUpdating = True

End Sub

 

[Serafin54]

sorry. I was a bit unclear. The yellow was just to show which cells would be problems. That isn't actually part of the report.