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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
What is the 'special' character?
0
finnstoneAuthor Commented:
any character not a-z or 0-9
0
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NorieAnalyst Assistant Commented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
NorieAnalyst Assistant Commented:
finnstone

Do you actually want to delete entire rows or do you just want to clear column A of special characters?
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.