CODEWITHSUNDEEP
excelvbafilter
VBAbyMBA
VBAbyMBA

Reputation: 826

Exclude excel filter from auto filter

This code works fine:

Sheet2.UsedRange.AutoFilter 28, Bad_list_a(), xlFilterValues

However I want to "<>" Exclude Bad_list_a() from autofilter, any suggestion?

Upvotes: 0

Views: 233

Answers (1)

Shivang Gupta
Shivang Gupta

Reputation: 347

This is alternative way to exclude array from autofilter.

titles is an array that contains the values to exclude from autofilter. in your case Bad_list_a(). This code copy the column (that you want to filter) to sheet1, remove duplicates.

Then it deletes all the cells that matches with titles array. Finally, it will autofilter the column with remaining values that you need.

titles = Array("string1", "string2", "string3", "string4", "string5", "string6", "string7", "string8", "string9")
    .Columns(columnToFilter).Copy
        sheet1.Range("J1").PasteSpecial xlPasteValues
        sheet1.Range("J:J").RemoveDuplicates 1, xlYes
    
 Set rngTitle = macroSheet.Range("I:I")
    rngTitle.Value = Application.Transpose(titles)
    
    For Z = 1 To sheet1.Cells(Rows.Count, 10).End(xlUp).Row
    If Application.WorksheetFunction.CountIf(sheet1.Range("I:I"), sheet1.Cells(Z, 10).Value) > 0 Then
    sheet1.Cells(Z, 10).Delete
    Z = Z - 1
    End If
    Next Z
    
    lastrowMacro = macroSheet.Cells(Rows.Count, 10).End(xlUp).Row
    Set rngcrit = macroSheet.Range("J1:J" & lastrowMacro).SpecialCells(xlCellTypeVisible)
    vCrit = rngcrit.Value
    
    ''Apply filter for error values of title
    .Range("1:1").AutoFilter Field:=columnToFilter, Criteria1:=Application.Transpose(vCrit), Operator:=xlFilterValues

Upvotes: 1

Related Questions