delete row if special character in col A

finnstone
finnstone used Ask the Experts™
on
i have 340k rows. need to check value in COL A, if there is special character, delete that row

thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
What is the 'special' character?

Author

Commented:
any character not a-z or 0-9
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating 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

Ryan ChongSoftware Team Lead

Commented:
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.

Author

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

Author

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

Commented:
finnstone

Do you actually want to delete entire rows or do you just want to clear column A of special characters?
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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

Author

Commented:
EXCELLENT WORK!

Author

Commented:
THANKS MARTIN
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

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

"
,
.
:
;
(
)
&
!
-

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial