Using autofilters to delete rows with 2 criteria.

[siaoz19]

I have a set of datas all in a column A:A.
i would only like to have the values with n and ( at the start of the value to appear after filtering.

Currently my formula looks like this.

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the value that you want to delete
'Tip: use DeleteValue = "<>ron" to delete rows without ron
DeleteValue = "<>n*"
DeleteValue = "<>(*"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub</pre>the result i ended up with was only data starting with brackets.

How do i have 2 criteria not filtering each other out?

 

[siaoz19]

can anybody help? it will be greatly appreciated.

 

[ukmikeb]

Hi

Replace your DeleteValue Dim statement with -

Dim DeleteValue1 As String
Dim DeleteValue2 As String

Replace your DeleteValue allocation statement with -

DeleteValue1 = "<>n*"
DeleteValue2 = "<>(*"

Change your Autofilter statement to -

.Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue1, Operator:=xlAnd, Criteria2:=DeleteValue2

hth

 

[siaoz19]

If i want to add another criteria, how do i do it ?

Sub Delete_with_Autofilter()
Dim DeleteValue1 As String
Dim DeleteValue2 As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the value that you want to delete
'Tip: use DeleteValue = "<>ron" to delete rows without ron
DeleteValue1 = "<>n*"
DeleteValue2 = "<>(*"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue1, Operator:=xlAnd, Criteria2:=DeleteValue2

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

For example 2 or more criteria? and they don't filter out each other.

 

[Marcelo Branco]

If you want to delete rows whose cells begin with n or ( maybe
(try it on a test-workbook)

DeleteValue1 = "n*"
DeleteValue2 = "(*"

M.

 

[siaoz19]

sorry m, i think u've misunderstood my question.

the solution ukmikeb gave was working well for me but now instead of having 2 criteria,i would like to add in another criteria which is "<>T*". How do i add this criteria into the formula given by ukmikeb?

thanks =)

 

[Marcelo Branco]

sorry m, i think u've misunderstood my question.

the solution ukmikeb gave was working well for me but now instead of having 2 criteria,i would like to add in another criteria which is "<>T*". How do i add this criteria into the formula given by ukmikeb?

thanks =)

Sorry, i misunderstood your question.

M.

 

[siaoz19]

Could you help me out?

 

[Marcelo Branco]

In the same site (Ron de Bruin) where you found your code there is this:
http://www.rondebruin.nl/delete.htm#AutoFilter

"More than two Criteria

For more then two Criteria you can loop through the words in the array.
Note: This is also working if you use one word in the array.

Sub Delete_with_Autofilter_Array()
    Dim rng As Range
    Dim calcmode As Long
    Dim myArr As Variant
    Dim I As Long
 
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    'Fill in the values that you want to delete
    myArr = Array("ron", "Dave", "Jelle")
 
    For I = LBound(myArr) To UBound(myArr)
 
        'Sheet with the data, you can also use Sheets("MySheet")
        With ActiveSheet
 
            'Firstly, remove the AutoFilter
            .AutoFilterMode = False
 
            'Apply the filter
            .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)
 
            Set rng = Nothing
            With .AutoFilter.Range
                On Error Resume Next
                Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rng Is Nothing Then rng.EntireRow.Delete
            End With
 
            'Remove the AutoFilter
            .AutoFilterMode = False
        End With
 
    Next I
 
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
 
End Sub

M.

 

[Marcelo Branco]

-Edit-

i think the code above will not work for "<>something"

M.