How to eliminate rows with foreign text characters ?

[Mark53]

Hi, people,

Using Excel 2007,
I have one column of about 1500 people's first names.
Some include foreign characters, like the "e" with a slant over it.
I want to eliminate/delete all rows that have anything except normal English
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
in them. How to do that, please?

Thanks.

Mark53

 

[Domenic]

Try...

[font=Verdana]
[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] j [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [color=darkblue]For[/color] i = LastRow [color=darkblue]To[/color] 1 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Cells(i, "A").Value <> "" [color=darkblue]Then[/color]
            [color=darkblue]For[/color] j = 1 [color=darkblue]To[/color] Len(Cells(i, "A").Value)
                [color=darkblue]If[/color] Abs(Asc(UCase(Mid(Cells(i, "A").Value, j, 1))) - 77.5) <= 12.5 [color=darkblue]Then[/color]
                    [color=green]'Do nothing[/color]
                [color=darkblue]Else[/color]
                    Cells(i, "A").EntireRow.Delete
                    [color=darkblue]Exit[/color] [color=darkblue]For[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Next[/color] j
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[Mark53]

Wow.

OK. Thanks very much, Domenic.

 

[Domenic]

You're welcome! Thanks for the feedback!