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.