CODEWITHSUNDEEP
vbaexcel
Mr.Riply
Mr.Riply

Reputation: 845

Excluding values in a filter even if the value is not there

I have many files to loop through. In each file I have to filter out specific values.

Example. I have to filter out numbers 1, 3, 5, 7, 9 and keep everything else

beginning 

1
2
3
4
9
11
15

result

2
4
11
15

I came up with this code, but it proved to be useless as its not working 

    Rows("1:1").Select
    Selection.AutoFilter
    wkb.Sheets("temp").Range("$A$1:$AC$72565").AutoFilter Field:=9, Criteria1:="Main"
    wkb.Sheets("temp").Range("$A$1:$AC$72565").AutoFilter Field:=10, Criteria1:="C"
    wkb.Sheets("temp").Range("$A$1:$AC$72565").AutoFilter Field:=11, Criteria1:="N"

'this part of the code is where I try to exclude values
    wkb.Sheets("temp").Range("$A$1:$AC$72565").AutoFilter Field:=1, Criteria1:=Array("<>1", "<>3", "<>5", "<>7", "<>9")

Upvotes: 0

Views: 76

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37337

Try this (all necessary comments in code):

Sub filter()
'declaration of variables
Dim numbers(5) As Long, cell As Range, number As Variant
'fill this array accordingly to your needs (notee that you'd have to change array size in declaration
numbers(0) = 1
numbers(1) = 3
numbers(2) = 5
numbers(3) = 7
numbers(4) = 9
'here we loop through all cells in frist row, this lenghty formule is for finding last fill column in first row
For Each cell In Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
    'here we loop through specified numbers to filter out these values
    For Each number In numbers
        If number = cell.Value Then
            'if match is found, clear the cell and leave the loop
            cell.Value = ""
            Exit For
        End If
    Next
Next
End Sub

Upvotes: 0

warner_sc
warner_sc

Reputation: 848

Consider inverse the logic and just filter what you want to delete?

Sub test()

Dim rng As Range

Set rng = wkb.Sheets("temp").Range("$A$1:$AC$72565")

rng.AutoFilter Field:=1, Criteria1:=Array("1", "3", "5", "7", "9"), Operator:=xlFilterValues
' In case your data has header used Offset(1,0) to preserve the header on the first row
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
rng.AutoFilter

End Sub

Upvotes: 1

Related Questions