delete row if special character in col A

i have 340k rows. need to check value in COL A, if there is special character, delete that row

thanks!
finnstoneAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Perhaps someone cleverer than I am can improve on this but try this. BTW it displays how far along it is in the StatusBar.
Sub DeleteRows()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngChar As Long
Dim lngProgress As Long

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

For lngRow = lngLastRow To 1 Step -1
    On Error Resume Next
    For lngChar = 1 To Len(Cells(lngRow, "A"))
        lngProgress = lngProgress + 1
        If (lngLastRow - lngRow) Mod 1000 = 0 Then
            Application.StatusBar = lngLastRow - lngRow & " of " & lngLastRow & " processed"
        End If
        If Err.Number <> 0 Then
            Cells(lngRow, "A").EntireRow.Delete
            Exit For
        End If

        Select Case Asc(Mid(Cells(lngRow, "A"), lngChar, 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
                Exit For
        End Select
    Next
Next
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
What is the 'special' character?
0
 
finnstoneAuthor Commented:
any character not a-z or 0-9
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Martin LissOlder than dirtCommented:
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

Open in new window

0
 
NorieVBA ExpertCommented:
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

Open in new window

0
 
Ryan ChongCommented:
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.
0
 
finnstoneAuthor Commented:
hi Martin, how long do you think it would take for that to run? still running 15 minutes later..
0
 
finnstoneAuthor Commented:
looks like it eliminated 40k rows, but still many rows left to eliminate . see attached
genderiee2.xlsm
0
 
NorieVBA ExpertCommented:
finnstone

Do you actually want to delete entire rows or do you just want to clear column A of special characters?
0
 
finnstoneAuthor Commented:
EXCELLENT WORK!
0
 
finnstoneAuthor Commented:
THANKS MARTIN
0
 
Martin LissOlder than dirtCommented:
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
0
 
finnstoneAuthor Commented:
martin, is it possible to edit this one? i would like to NOT delete cells that have the following special characters -

"
,
.
:
;
(
)
&
!
-
0
 
finnstoneAuthor Commented:
so really just going after the CRAZY special charactesr like chinese symobls and copyrtight symbols
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.