Filter cells with special features

Question

I want to be able to filter column A. (with vba for delete what I don’t need)

Example:

• df!gf:mqichgfdcg
• test)2:@1jhbh5@j0
• est@56:)hquct36A
• h@hy.ju:A3)nxhd123QW
• tempghj#b:jkb
• temp234!A:gfgcjhgcj,hgk
• hgdfht:2345vk!
• hgchghc:268678954
• hgchghc:A268678954

I desire filter with these specification:

1. The filter should start after character : (for each cell)

2. There must be at least 10 characters (uppercase, lowercase, numbers, special characters)

https://i.stack.imgur.com/v7ooi.jpg this cells (A:3, A:5, A:7, A:8) don't respects the criterias

3. Erase lines that do not respect the criterias.

So i desire delete this cells. https://i.stack.imgur.com/kR40B.jpg

i desire delete each empy cells or lines https://i.stack.imgur.com/aj9qr.jpg

I have this code for delete each empy line

Source : Excel VBA - Delete empty rows Option Explicit

Sub Sample()
    Dim i As Long
    Dim DelRange As Range

    On Error GoTo Whoa

    Application.ScreenUpdating = False

    For i = 1 To 1000000
        If Application.WorksheetFunction.CountA(Range("A" & i & ":" & "B" & i)) = 0 Then
            If DelRange Is Nothing Then
                Set DelRange = Range("A" & i & ":" & "B" & i)
            Else
                Set DelRange = Union(DelRange, Range("A" & i & ":" & "B" & i))
            End If
        End If
    Next i

    If Not DelRange Is Nothing Then DelRange.Delete shift:=xlUp
LetsContinue:
    Application.ScreenUpdating = True

    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Answer 1

You may be overthinking this.  I’m going to ignore the VBA part of the question for a moment.  You can filter the strings using Excel’s builtin filtering capability:

1. Make sure that the strings are in a column with a header (e.g., set A1 to “Strings”) and filter the column.

2. Click on the drop-down arrow for the filter, → “Text Filters” → “Contains…”.

       

3. Enter :?????????? into the Contains filter.  That will match any value that contains a : followed by ten more characters.

Answer 2

You can use something like this code:

Sub test1()
    Dim OriginText, filterVal, startPosition
    Dim ThereIs10Char As Boolean
    Application.ScreenUpdating = False

    For i = 1 To Cells.Rows.Count ' this will be slow ,you better use integer number ( rows count number) instead of Cells.Rows.Count
        OriginText= Cells(i, "A").Value
        startPosition = InStr(1, OriginText, ":")
        filterVal = Mid(OriginText, startPosition + 1, Len(OriginText) - startPosition)
        ThereIs10Char = False
        If Len(filterVal >= 10) Then
            ThereIs10Char = True
        End If

        'I dont understand your mean for empty lines
        'you can use If condition for [while cells(i,"A").value="" Goto next i] or anything else

        If ThereIs10Char = True Then
            Rows(i).Delete Shift:=xlUp
            i = i - 1
        End If
    Next

    Application.ScreenUpdating = True
End Sub