using Excel autofilter to exclude items

[colmkav]

Hi,

how do I exclude items using VBA code? I thought I would get the code using record macro but it only gave code to include items.

eg
Range("A13").Select
ActiveSheet.Range("$A$13:$T$763").AutoFilter Field:=1, Criteria1:=Array( _
"10125014", "11394750", "2234200120", "2234600120", "52039100", "52080100", _
"54004912", "54004916"), Operator:=xlFilterValues

I would like to exclude 3 items

Colm

[GS]

Post by colmkav
Hi,
how do I exclude items using VBA code? I thought I would get the code
using record macro but it only gave code to include items.
eg
Range("A13").Select
ActiveSheet.Range("$A$13:$T$763").AutoFilter Field:=1,
Criteria1:=Array( _ "10125014", "11394750", "2234200120",
"2234600120", "52039100", "52080100", _ "54004912",
"54004916"), Operator:=xlFilterValues
I would like to exclude 3 items
Colm

That would be contra the purpose AutoFilter was designed for. IOW, it
will only select/include according to your specified criteria. To
accomplish this with VBA would be easy as hiding all rows that contain
the value you want to exclude. (This has nothing to do with using
AutoFilter)...

Sub FilterExcludedData()
Dim vDataIn, n&
Const sExcludes$ = "10125014,11394750" _
& ",223400120,2234600120" _
& ",52039100,52080100,54004912,54004916"

vDataIn = Range("A1", Cells(Rows.Count, 1).End(xlUp))
For n = LBound(vDataIn) To UBound(vDataIn)
If Len(vDataIn(n, 1)) Then
If InStr(1, sExcludes, vDataIn(n, 1)) > 0 Then _
Rows(n).Hidden = True
End If
Next 'n
End Sub

..wherein colA is assumed to contain the values you want to filter on.
Empty cells are ignored. You can modify this to toggle the filter
and/or prompt the user for the exclude values.

[GS]

Note that the code sample was generated in Excel and so the necessary
refs for VB6 automation need to be implemented appropriately for the
objects being referenced!