VBA code to filter for values with specific character types in the text string (alpha-numeric)

[dougdrex]

Hi everyone, I'm using Excel 2010 VBA.

What I need to do is filter my data set in two separate columns (Q and S) for values that contain 7 total characters, beginning with 2 alpha characters followed by 5 numeric characters (i.e. AB12345).

All rows that do not contain values with that specific formatting in the value should be deleted.

I have searched the internet and have not found anything that addresses this type of VBA code. Does anyone have any ideas?


Thanks!
Doug

 

[MrKowz]

To be sure I understand correctly, would the condition to keep a row be that BOTH Q and S meet the Alpha/Numeric criteria? Or that Q or S meet the criteria?

 

[dougdrex]

If either Q or​ S meet the criteria, I would want to keep the row.

 

[MrKowz]

Untested, but give this a shot:

Public Sub ClearRows()
Dim i       As Long, _
    LR      As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

LR = Range("Q" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("Q" & i).Value Like "[A-Z][A-Z]#####" Or Range("S" & i).Value Like "[A-Z][A-Z]#####" Then
        'Keep Row
    Else
        Rows(i).Delete shift:=xlUp
    End If
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub