How to custom filter using a macro

Requirement:

The user needs to write a macro that basically acts as a custom filter.

If column [dependent type] AND [dependent number] are empty AND [column index] is NOT equal to 1.

The user needs o delete that row from the data set.

The file the user is attaching is a smaller version of the original so the macro needs to be pretty quick.

The file is attached - please use the link below.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149939-custom-filter-using-a-macro

Solution:

Code:

Dim lRow As Long
ActiveSheet.AutoFilterMode = False
lRow = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet.Range("$A$1:$S$" & lRow)
    .AutoFilter Field:=19, Criteria1:="="
    .AutoFilter Field:=18, Criteria1:="="
    .AutoFilter Field:=17, Criteria1:="<>1"
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    
End With
ActiveSheet.AutoFilterMode = False

An alternative method which may be faster if your actual data is large (100,000 rows +) as it does not use filtering.

Code:

Sub DeleteRows()
    Dim x, i As Long
    
    With Sheets("Sheet1").Cells(1).CurrentRegion '// CHANGE SHEET NAME TO SUIT
        x = .Value
        For i = 2 To UBound(x, 1)
            If x(i, 17) <> 1 And x(i, 18) = "" And x(i, 19) = "" Then x(i, 17) = ""
        Next
        .Value = x
        .Columns(17).SpecialCells(4).EntireRow.Delete
    End With
    
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by nyatiaju and KjBox.