VBA autofilter to filter cells of strings (that contain numbers) and numbers

Question

first time as a user here and begginer at VBA, so please be pattient.

I've got a list I wanna filter using VBA that has both numbers and strings (some of them containing numbers as part of an ID) in the column I'm ussing to apply the filter. If the filterValue is a number or a digit, I wanna be able to find it within the numbers or strings in the column. For example, if three cells have the values "3231" (as a number), "MN23" and "AR234IT" (as two strings), I should get the three of them with filterValue=23 (also with 2 or 3).

If I use "" & filterValue & "" as Criteria1, it works for the strings but it doesn't return the rows that contain number formatted cells in the filtered column. I solved it by adding ' to number cells (turning them to text format), saving their original format indicator in an array, filtering by "" & filterValue & "" and then removing the ' from every string cell that was originally a number.

Here's the code:

rowCount = filteredRegion.Rows.Count
ReDim isNumberArray(1 To rowCount) As Boolean
For i = 1 To rowCount
    If Application.WorksheetFunction.IsNumber(filteredRegion(i, 3)) And filteredRegion(i, 3) <> "" Then
        filteredRegion(i, 3) = "'" & (filteredRegion(i, 3))
        isNumberArray(i) = True
    Else
        isNumberArray(i) = False
    End If
Next i
filteredRegion.AutoFilter Field:=3, Criteria1:="*" & filterValue & "*"
For i = 1 To rowCount
    If IsNumeric(filteredRegion(i, 3)) And filteredRegion(i, 3) <> "" And numberArray(i) Then
        filteredRegion(i, 3) = CDbl(filteredRegion(i, 3))
    End If
Next i

This works. I get rows filtered out that have the filterValue somewhere within them, wheter they have numeric or text content, and I keep their original format after the VBA call.

But, besides being totally awful and inefficient, it works as long as the cells edited don't have a formula that returns a number. If they do, the formula gets erased and I just get its value back. I guess I could save the formula and retrieve it but still sounds awful. Besides, I have array Formulas in one column so it would get pretty nasty. Can someone help me out? Is there a way in which I don't edit the cells at all?

Answer 1

This snippet hide all rows which contains in part a defined text in a specific column:

Sub myfilter()

Set filteredrange = Range("A56:H62")
Lookup = "23"
Colvalue = 3

For i = 1 To filteredrange.Rows.Count
If InStr(1, filteredrange.Cells(i, Colvalue), Lookup) = 0 Then
filteredrange.Rows(i).Hidden = True
End If

Next i

End Sub

To unhide them:

filteredrange.Rows.Hidden = False

Answer 2

Filter If Digit in Column

Sub FilterIfDigit()

    Const SHEET_NAME As String = "Sheet1"
    Const FILTER_COLUMN As Long = 3

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Sheets(SHEET_NAME)
    ws.AutoFilterMode = False
    
    Dim trg As Range: Set trg = ws.Range("A1").CurrentRegion
    
    Dim FilterCol() As Variant: FilterCol = trg.Columns(FILTER_COLUMN).Value

    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim rValue As Variant, rString As String, r As Long
     
    For r = 2 To trg.Rows.Count
        rValue = FilterCol(r, 1)
        If Not IsError(rValue) Then
            rString = CStr(rValue)
            If rString Like "*#*" Then
                dict(rString) = Empty
            End If
        End If
    Next r

    If dict.Count = 0 Then ' no digit found
        trg.AutoFilter FILTER_COLUMN, "0123456789" ' any digit(s)
        Exit Sub
    End If
   
    trg.AutoFilter FILTER_COLUMN, dict.Keys, xlFilterValues
    
End Sub

Answer 3

If you don't mind to use a helper column, maybe you want to try this :

Sub test()
Dim col As String, crit As String, rg As Range
col = "c1"
crit = "=*" & Application.InputBox("input the criteria") & "*"
Set rg = Range("A1", Range("E" & Rows.Count).End(xlUp))

With Range("ZZ1").Resize(rg.Rows.Count)
    .Value = "=text(" & col & ",0)"
    
    Application.ScreenUpdating = False
    .Copy: .PasteSpecial xlValues
    Application.ScreenUpdating = True

    .AutoFilter Field:=1, Criteria1:=crit, Operator:=xlAnd
    
    MsgBox "filtered"
    .AutoFilter Field:=1
    .ClearContents
End With
End Sub

col is the column to filter row 1.
crit it the criteria to filter, inputted by user
rg is the range of the table

Helper column is column ZZ:.
put a formula to convert number to text
copy the formula and paste it as value.
filter with crit.
show the msgbox.
clear the filter and clear the contents of helper column.