delete row if special character in col A

What is the 'special' character?

any character not a-z or 0-9

Sub DeleteRows()
Dim lngLastRow As Long
Dim lngRow As Long

Application.ScreenUpdating = False
lngLastRow = Range("A1048576").End(xlUp).Row

For lngRow = lngLastRow To 1 Step -1
    Select Case Asc(Left(Cells(lngRow, "A"), 1))
        Case 48 To 57, 65 To 90, 97 To 122
            ' OK
            ' 0 to 9, A to Z, a to z
        Case Else
            Cells(lngRow, "A").EntireRow.Delete
    End Select
Next
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

If you only have single characters in column A then perhaps you could use this.

Dim I As Long

    For I = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("A" & I).Value Like "[!0-9A-Za-z]" Then
            Rows(I).Delete
        End If
    Next I

Select allOpen in new window

340k rows of data for comparison? I would write a .qry (Microsoft query) to generate the output instead.

that could be faster than VBA codes to check row by row.

hi Martin, how long do you think it would take for that to run? still running 15 minutes later..

looks like it eliminated 40k rows, but still many rows left to eliminate . see attached
genderiee2.xlsm

finnstone

Do you actually want to delete entire rows or do you just want to clear column A of special characters?

EXCELLENT WORK!

THANKS MARTIN

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017

martin, is it possible to edit this one? i would like to NOT delete cells that have the following special characters -

"
,
.
:
;
(
)
&
!
-

so really just going after the CRAZY special charactesr like chinese symobls and copyrtight symbols