Delete Row If Contains Any Letter A-Z.

**superdupersk88tz** · 08-20-2012, 11:46 PM

Hello,
Im having a terrible time trying to find anything on something like this and i would appreciate any help i could get.
I have a list in column a of sheet1 like this

11-06515
mitchell
62-06055
62-06079
KR3W.ai
LRG.psd

i would like to run a macro to search column A and if it contains a letter it would delete the entire ROW so the end result would end up like this.

11-06515
62-06055
62-06079

**nilem** · 08-21-2012, 12:20 AM

try it

Sub ert()
Dim r As Range, rDel As Range
Set rDel = Range("B1")
For Each r In Range("A1", Cells(Rows.Count, 1).End(xlUp))
    If r.Value Like "*[a-zA-Z]*" Then Set rDel = Union(rDel, r)
Next
If Not rDel Is Nothing Then Intersect(Range("A:A"), rDel).EntireRow.Delete
End Sub

**superdupersk88tz** · 08-21-2012, 12:30 AM

WOW! I cant believe it, you are amazing my friend.

**JapanDave** · 08-21-2012, 12:33 AM

Try this,

Option Explicit

Sub ParseData()
    Dim rCell            As Range
    Dim cel
    Dim x                As Range
    

    Application.ScreenUpdating = False
    
    With CreateObject("vbscript.regexp")
        .Global = True
           For Each rCell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            .Pattern = "[a-zA-Z]"
            Set cel = .Execute(rCell)
             Debug.Print cel.Count
             If cel.Count <> 0 Then
                If x Is Nothing Then
                  Set x = rCell
                Else
                  Set x = Union(x, rCell)
                End If
             End If
        Next rCell
    End With
      x.EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

Edit: Never mind nilem has sorted you out.