Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    California
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    California
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub YourMacroName()
    'Paste code here
    End Sub
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    California
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hey Al,

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

    Any suggestions?

  9. #9
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)?

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •