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

lkanarow@umich

New Member
Joined
Mar 28, 2002
Messages
6
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top