Question

第一次作为这里的用户和VBA的乞讨者,因此,请上下药。

我用VBA在I m 炉 us一栏中列出了一个清单,该清单中既有数字也有体力(其中一些含有数字作为ID的一部分)。如果过滤器是数位数或数位数,则I wanna能够在该列的号码或体内找到。例如,如果三个单元的数值为“3231”(作为数字)、“MN23”和“AR234IT”(作为两个阵列)的话,我就应当将其中的三个单元带上过滤器=23(也有两个或三个)。

如果我使用“和amp;过滤器和安插;“”作为标准1,则它就座标开展工作,但是它并没有把装在过滤器栏内装具编号的囚室的囚室退回。我通过在数字电池中添加(将其放在文字格式上)、在阵列中保留其原始格式指标、过滤“和amp”、“,然后从原先人数的每个扼杀室中删除。

该法典:

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

这一工作。我的浏览器被过滤,有些地方有过滤器,他们有数字或文字内容,我在VBA打电话后保持原来的格式。

但是,除了完全富有成效和效率低下外,只要经编辑的囚室没有一套回归的公式,它就能够运作。如果他们这样做,这个公式就会消失,我只是收回其价值。我猜测,我可以挽救这一公式,取而代之,但依然不错。此外,在一栏中,我有阵列办法,这样会很快出现。谁能帮助我? 是否有办法让我根本不把囚房?开?

Answer 1

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 2

这张旗号掩盖了部分包含特定一栏定义案文的所有行文:

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

宣布:

filteredrange.Rows.Hidden = False

Answer 3

如果你不希望使用助手栏,你可能想尝试:

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.

Filter If Digit in Column

友情链接