Test for email addresses
That did it. Thanks
"Bob Phillips" wrote:
You shouldn't store it in the sheet code module, but in a standard code
module, it is a UDF.
You use it in the worksheet by, assuming that the address are in column A,
in B1 put =ValidEmail(A1) and copy down, then filter column B by False
and delete the visible rows.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"sb1920alk" wrote in message
...
Ok, I'm lost. I copied your code, right-clicked my tab name and then
pasted it.
What do you mean, "just add =ValidEmail(A1) in B1 say."
Where do I add it?
Thanks,
"Bob Phillips" wrote:
Here is a function that tests it
'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern =
"^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function
just add =ValidEmail(A1) in B1 say.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"sb1920alk" wrote in message
...
I have a large (~10,000) list of former clients in an excel file that
was
created when I pulled a report from my third party software. I've
contracted
with a newsletter company to help stay in touch with them. The
newsletter
company charges by entry so I need to delete any rows that aren't
email
addresses. I've already found the marco that deletes duplicate
entries,
and I
can easily sort to get rid of entries with no email address, now I
need
some
way to delete entries that have something other than an email address
in
the
"email address" column.
The answer could be several things: a helper column that tests for
wildcard@wildcard, or a macro that tests and deletes rows. I haven't
been
able to get anything to work yet. Any help is appreciated.
Regards,
|