how to use VBA try to use filter field2: criteria= “0/01/1900”

Question

Column B is the date. I want to select all the cell contain “00-01-1900” and the delete the entire row. Range(“a9:n9”).autofilter field:2 criteria= “00-01-1900”

But the filer is not working properly. I could not select it.

Could anyone help to solve it?

Answer 1

I assumed you have a header in the first row, and that your data begins at column A.

Sub deleteData()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("yourSheetsName")
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Dim lastCol As Long: lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    ws.Rows.Hidden = False
    
    With ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
        .AutoFilter field:=2, Criteria1:="00-01-1900"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    If ws.AutoFilterMode Then ws.AutoFilterMode = False

    ws.UsedRange.Calculate

End Sub

Good Luck