CODEWITHSUNDEEP
vbaexcel
drLecter
drLecter

Reputation: 197

Filtering on 2 not equal wildcards. Possible?

is it possible to filter the working table on two criteria that are wildcards. I want to delete all rows from my date that have Ident number in column C not starting with 1 or 4. After filtering and deleting I want to show all data again and have no blank rows in between

Is that even possible? I was trying a lot of stuff. I am posting 2 options that I thought were most likely to work - but didnt. Any info is greatly appreciated. 

With ActiveSheet
'FIRST TRY
.AutoFilter Field:=3, Criteria1:="<>1*", Operator:=xlOr, Criteria2:="<>4*"

'SECOND TRY
.AutoFilter Field:=3, Criteria1:=Array( _
    "<>1*", "<>4*"), Operator:=xlFilterValues

'THEN I WANT ALL ROWS THAT DONT START WITH 1 OR 6 DELETED
.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
End With

Upvotes: 1

Views: 2139

Answers (3)

Comintern
Comintern

Reputation: 22195

As mentioned by Tim Williams in the comment, you can't apply a text filter to a numeric column. I personally wouldn't use a filter for this in either case, as there's a dead simple VBA only approach:

Dim current As Long
Dim sheet As Worksheet
Dim start As String

Set sheet = ActiveSheet
With sheet
    For current = .UsedRange.Rows.Count To 2 Step -1
        start = Left$(.Cells(current, 3), 1)
        If start <> "1" And start <> "4" Then
            .Rows(current).Delete
        End If
    Next
End With

EDIT: If the sheet is sorted, the row deletions are a lot faster, because you can track the start and end of ranges that need to be deleted instead of removing potentially thousands of individual rows:

Dim current As Long
Dim sheet As Worksheet
Dim start As String
Dim bottom As Long

With sheet
    For current = .UsedRange.Rows.Count To 2 Step -1
        start = Left$(.Cells(current, 3), 1)
        If start <> "1" And start <> "4" And bottom = 0 Then
            bottom = current
        ElseIf (start = "1" Or start = "4") And bottom <> 0 Then
            .Range((current + 1) & ":" & bottom).Delete
            bottom = 0
        End If
    Next
End With

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166316

This worked for me:

ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=3, _
            Criteria1:="<>1*", Operator:=xlAnd, Criteria2:="<>4*"

Col C must be formatted as "Text" - you may have to re-enter the information to be sure (you should see the grren "number stored as text" indicator on those cells)

Upvotes: 0

Mitch
Mitch

Reputation: 544

Try it as two separate filters. I have not testing this code, but am wondering if it will maintain the first filter and add the second filter.

'Filter 1
    .AutoFilter Field:=3, Criteria1:="<>1*", Operator:=xlFilterValues
'Filter 2
    .AutoFilter Field:=3, Criteria2:="<>4*",  Operator:=xlFilterValues

Upvotes: 1

Related Questions