Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #1759

Re: using Excel autofilter to exclude items

Show all headers | View raw

> 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.

-- 
Garry

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

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

using Excel autofilter to exclude items colmkav <colmjkav@yahoo.co.uk> - 2013-04-12 01:27 -0700
  Re: using Excel autofilter to exclude items GS <gs@somewhere.net> - 2013-04-12 11:47 -0400
    Re: using Excel autofilter to exclude items GS <gs@somewhere.net> - 2013-04-12 12:43 -0400
  Re: using Excel autofilter to exclude items "Auric__" <not.my.real@email.address> - 2013-04-12 22:38 +0000

csiph-web