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?

 

[Marcelo Branco]

Assuming
Sheet name = Sheet1 (can adjust)
Column A: Header in row 1, data beginning in row 2 (can adjust)

The code below (based in Ron de Bruin Loop_Example) deletes rows whose cells in column A doesnt start with n, N, t, T or (

(try it on a test-workbook)

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
 
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    'Adjust this to your sheet name
    With Sheets("Sheet1")
 
        'We select the sheet so we can change the window view
        .Select
 
        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
 
        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False
 
        'Set the first and last row to loop through
        Firstrow = 2
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
 
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
 
            'We check the values in the A column in this example
            With .Cells(Lrow, "A")
 
                If Not IsError(.Value) Then
                    If UCase(Left(.Value, 1)) <> "N" And _
                    Left(.Value, 1) <> "(" And _
                    UCase(Left(.Value, 1)) <> "T" Then .EntireRow.Delete
                End If
 
            End With
 
        Next Lrow
    End With
 
End Sub

HTH

M.

 

[siaoz19]

we try the above method, but it still won't work. Do you have any other method ?

 

[Marcelo Branco]

we try the above method, but it still won't work. Do you have any other method ?

It worked for me. What happened to you? Error?

Could you post a sample of your data?

M.

 

[Marcelo Branco]

Sorry, when i pasted the macro something wrong has happened

Missed this part
ActiveWindow.View = ViewMode

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




This is correct

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
 
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    'Adjust this to your sheet name
    With Sheets("Sheet1")
        'We select the sheet so we can change the window view
        .Select
        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False
        'Set the first and last row to loop through
        Firstrow = 2
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
 
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the A column in this example
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If UCase(Left(.Value, 1)) <> "N" And _
                    Left(.Value, 1) <> "(" And _
                    UCase(Left(.Value, 1)) <> "T" Then .EntireRow.Delete
                End If
            End With
        Next Lrow
    End With
 
    ActiveWindow.View = ViewMode
 
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
 
End Sub

M.

 

[siaoz19]

Yes!!!!! It works!!!! Thank you so much m!!!! Thankyouuuu!!!!

 

[Marcelo Branco]

Yes!!!!! It works!!!! Thank you so much m!!!! Thankyouuuu!!!!

You are welcome. Glad to help

M.