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

[Asala42]

Hiya,

Could you get away with auto-filters instead? -(I'm not sure if you're dealing with a lot more than 1 column per sheet)

In one column, for example, you can go to toolsautofilter then click on the arrowcustomcontains @ - then you could just copy the list of email addresses to another sheet. repeat per column.

Hope that helps somewhat
Adam

[Al Chara]

Try the following:

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

Always make a copy of your workbook before testing out macros.

[AJ]

How about this. It's not exactly refined but it works

Sub test()

'Assuming emails are all in Column A and Column B is blank
'Assume first row to look at is row 1
'(Adjust as you like)

Dim Lastrow As Integer


'Find end point
Lastrow = Range("A65536").End(xlUp).Row


'Fill in a formula down column B that tells us
'whether the value in A is valid or not (i.e. contains @)
Range("B1").FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""@"",RC[-1])),1,0)"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" & Lastrow)

'Remove unwanted cells
For i = 1 To Lastrow

Range("A" & i).Select
If ActiveCell.Offset(0, 1) = 0 Then
ActiveCell.ClearContents
End If
Next i

'Clear out Column B
Range("B1:B" & Lastrow).ClearContents

End Sub


Rgds
AJ

[AJ]

Oops. Sorry Al, didn't realise you'd beaten me to it!

[lkanarow@umich]

Hey Al,

I've tried doing that as a macro in the vba, but I keep getting a compile error.

should I paste the whole thing in or only part of it.

sorry,I'm not to good with excel

[Al Chara]

Sub YourMacroName()
'Paste code here
End Sub

[lkanarow@umich]

hey Al,

I am trying the macro again, but it keeps coming up as an error on the second line .Find

Any suggestions?

[Al Chara]

lkanarow@umich,

I can't make my code not work. I don't know what you are doing wrong. What does your data look like, how is it arranged (all in one column, row)?

[sxpeter]

This isn't as elegant as a macro, but you can enter the following formula in col B (assuming addresses are in col A) and copy down.

=IF(ISERR(SEARCH("@",A1)),"",SEARCH("@",A1))

It will return a number if @ is in the string and a blank if not. You can then sort by col B, which will put all blanks (non-email addresses)at the end, allowing you to easily delete those rows. Hope this is what you're looking for.

sxpeter