Macro for Filtering Data

[arpanpanchal]

Hi Friends

I have attached the file in which I want to filter the data where it does not contain 101, 102, 1022, 1023, 103, 104, 1055, 107, 1078, 110, 111.

The rest of the rows except the above should be deleted. Can you please help me writing macro?

Many thanks in advance.

[Badeea]

Hi arpanpanchal,

Try the following code and let me know if this is what you want.

Sub ArpanpanchalNumbers()
Dim ListCounts As Long
ListCounts = Range("A1", Range("A1").End(xlDown)).Rows.Count
For i = 2 To ListCounts
Select Case Cells(i, "A")
Case 101
Case 102
Case 1022
Case 1023
Case 103
Case 104
Case 1055
Case 107
Case 1078
Case 110
Case 111
Case Else
Cells(i, "A") = ""
End Select
Next i
End Sub

[jindon]

This should do

Sub test()
    With Cells(1).CurrentRegion
        .AutoFilter 1, Split("101,102,1022,1023,103,104, 1055,107,1078,110,111", ","), 7
        On Error Resume Next
        .SpecialCells(12).Select
        .AutoFilter
        Selection.EntireRow.Hidden = True
        .SpecialCells(12).EntireRow.Delete
        On Error GoTo 0
        .Rows.Hidden = False
        .Cells(1).Select
    End With
End Sub

[arpanpanchal]

Hi Jindon

Thanks a lot. It works as I wanted. Can I please ask you to explain few things in your code as I am learning vba? Why did you use 7 after Split function and what is the meaning of SpecialCells(12)?

Thanks again for your assistance.

[jindon]

1) 7 for Operator protocol of AutoFilter is xlFilterValues and this is not optional when criteria is an array.
2) 12 for Type protocol of SpecialCells is xlCellTypeVisible.

Helped?

[arpanpanchal]

Hi Jindon

That make sense now.

Again, thank a lot for your time and effort.

[jindon]

You are welcome and thanks for the rep.