Ignoring blanks VBA macro

[schellett]

Hi, can anyone please advise how I can run this macro to ignore blank cells. Thank you

Sub ValidatePattern()

    Dim cell As Range, rng As Range
    Dim InvalidCount As Long, x As Long
    Dim blInvalid As Boolean

    x = 5    'Column to Validate

    Set rng = ActiveSheet.UsedRange.Columns(x).Offset(8).Resize(ActiveSheet.UsedRange.Rows.Count - 1, 1)

    For Each cell In rng.Cells
        blInvalid = False
        Select Case True
            Case UCase(cell.Value) Like "[A-Z][A-Z]##[A-Z][A-Z][A-Z]"
            Case UCase(cell.Value) Like "[A-Z]#[A-Z][A-Z][A-Z]"
            Case UCase(cell.Value) Like "[A-Z]##[A-Z][A-Z][A-Z]"
            Case UCase(cell.Value) Like "[A-Z]###[A-Z][A-Z][A-Z]"
            Case Else
                blInvalid = True
                'Highlight Invalid Cell Yellow
                cell.Interior.Color = RGB(255, 255, 0)

                'Add Instance to Invalid Counter
                InvalidCount = InvalidCount + 1
        End Select

    Next cell

    'Were there any invalid patterns found?
    If InvalidCount > 0 Then MsgBox "There were " & InvalidCount & _
     " cells found not following the required pattern!"


End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

Try adding the blue lines where shown.

For Each cell In rng.Cells
  If Len(cell.Value) > 0 Then
    blInvalid = False
    Select Case True
        Case UCase(cell.Value) Like "[A-Z][A-Z]##[A-Z][A-Z][A-Z]"
        Case UCase(cell.Value) Like "[A-Z]#[A-Z][A-Z][A-Z]"
        Case UCase(cell.Value) Like "[A-Z]##[A-Z][A-Z][A-Z]"
        Case UCase(cell.Value) Like "[A-Z]###[A-Z][A-Z][A-Z]"
        Case Else
            blInvalid = True
            'Highlight Invalid Cell Yellow
            cell.Interior.Color = RGB(255, 255, 0)

            'Add Instance to Invalid Counter
            InvalidCount = InvalidCount + 1
    End Select
  End If
Next cell

 

[schellett]

Hi Peter,

Thank you so much! Thank you for the tags also, much appreciated.

Regards

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.