Help...again. Need a macro to delete all cells without @ sy

[lkanarow@umich]

I have a massive excel file of about 300,000 e-mail addresses spread into 5 sheets. They are entries from contests and some of the e-mail addresses aren't really e-mail addresses. Is there a way for me to delete all cells in a column that DO NOT have the @ symbol?

If anyone knows a macro to this problem it would save me ALOT of time and tedious work, and it would be GREATLY appreciated.

thanks,
Lindsey

 

[lkanarow@umich]

Hey Al,

Maybe I am doing something wrong. I copy and pasted your code into the Visual Basic Editor starting with "With ActiveSheet.UsedRange" through to "Next cel"

Then I tried to run the macro, but I keep getting a default. Am I not supposed to copy and paste all of that into the VBA? Do I need to name the macro or add Sub/ end sub?

 

[tk19]

use the worksheet function method someone posted. then you won't have to mess around with the vb code.

 

[Al Chara]

Yes you do have to put in between Sub and end sub:

Sub YourMacroName()
With ActiveSheet.UsedRange
Set c = .Find("@", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Font.ColorIndex = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

For Each cel In ActiveSheet.UsedRange
If cel.Font.ColorIndex <> 5 Then cel.ClearContents
Next cel
End Sub