CODEWITHSUNDEEP
excelvbaexcel-formula
Pablo
Pablo

Reputation: 23

Filter cells with special features

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

Example:

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.sstatic.net/v7ooi.jpg this cells (A:3, A:5, A:7, A:8) don't respects the criterias

  1. Erase lines that do not respect the criterias.

So i desire delete this cells. https://i.sstatic.net/kR40B.jpg

i desire delete each empy cells or lines https://i.sstatic.net/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

Upvotes: 1

Views: 62

Answers (2)

G-Man Says 'Reinstate Monica'
G-Man Says 'Reinstate Monica'

Reputation: 908

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…”.

        illustration of Filter GUI

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

Upvotes: 1

Sharif Lotfi
Sharif Lotfi

Reputation: 574

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

Upvotes: 0

Related Questions