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]

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.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[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!

[Auric__]

Adding to what Garry said, I'd like to point you to this group:

microsoft.public.excel.programming

The Excel VBA experts generally dwell there.

--
The average person is simply not equipped to decide
what's best for everybody.