Home > Enterprise >  Is it possible to filter with the condition contains in VBA?
Is it possible to filter with the condition contains in VBA?

Time:03-30

I have a sheet with data from column B to F. In the column F I have the data in this format:

2
2;4
2;4;8
...

I would like to knw if there is some way to filter in VBA to show only the rows that contains 4 in the column F, for example, or the columns that contains 8... and so on.

Thanks.

EDIT:

I am trying to iterate the rows with the data, but when I try to hide a row, I get an error that tells that I can't est the Hidden property of the class Range.

My code is this:

Private Sub cmbRevisiones_Change()

    

    Dim miRevision As String
    Dim miUltimaFila As Integer
    
    
    miRevision = "4"
    miUltimaFila = 185
    
    
    For miIterador = 14 To miUltimaFila
    
        Dim misRevisiones As String
        misRevisiones = Me.Cells(miIterador, 6)
        
        If InStr(misRevisiones, miRevision) > 0 Then
            Rows(miIterador).Hidden = True
        End If
    Next miIterador
    
    
    
End Sub

CodePudding user response:

This should do the trick:

Sub filter()
    Dim row As Long
    Dim s As Worksheet
    Set s = ActiveSheet ' work on the active sheet
    'Set s = Worksheets("Sheet1") 'work on a specific sheet
    
    For row = s.Cells(s.Rows.Count, 1).End(xlUp).row To 1 Step -1
        If InStr(1, ";" & s.Cells(row, "F").Value & ";", ";4;") > 0 Then
        s.Rows(row).Hidden = True
        End If
    Next
  
End Sub

The key here is the use of the InStr function to see if the value of the cell in column F contains 4. By looking at a string from the cell in column F with a semicolon prepended and appended, we can look for ";4;" and it will find it even if the 4 is the first or last value in the list.

  • Related