Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter 'not contain...' value (2000)

    Have 2 problem...

    Problem1) why the condition in filter not is repescted...????
    Problem2) why disapper the little "arraw" of filter...????

    Sub FastFilterDelete()

    Dim FilterRange As Range
    Dim FilterValue As String

    FilterValue = INVIO_EMAIL.CAT_AREA.Text

    With ActiveSheet
    Set FilterRange = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
    .AutoFilterMode = False
    'Selection.AutoFilter Field:=4, Criteria1:="<>*CAMPANIA SUD E CALABRIA*", _
    Operator:=xlAnd
    .Range("A1").AutoFilter Field:=4, Criteria1:="<>* FilterValue *", _
    Operator:=xlAnd
    On Error Resume Next
    FilterRange.SpecialCells(xlCellTypeVisible).Entire Row.Delete
    On Error GoTo 0
    .AutoFilterMode = False
    End With

    End Sub

    If effect i want to use the filter tath not contain the value FilterValue

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter 'not contain...' value (2000)

    Since you put FilterValue within the quotes, it is interpreted as literal text. You must use concatenation:

    ... Criteria1:="<>*" & FilterValue & "*" ...

    The line .AutoFilterMode = False hides the filter arrows.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter 'not contain...' value (2000)

    Great!

    but for curiosity...
    this statement is required in filter line?:
    Operator:=xlAnd

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter 'not contain...' value (2000)

    No, it's optional, and xlAnd is the default value so you can omit the Operator argument here.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter 'not contain...' value (2000)

    tks...

    But is possible to count the row NOT visible after filter before to showalldata?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filter 'not contain...' value (2000)

    COUNTA of the data in a column will count all the entries regardless of the filter

    The SUBTOTAL function can be used to count the visible items in a column

    The difference of the values is the the number of items NOT visible

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •