Using VB to remove criteria if <>

[sissy60504]

Good morning all,

I have a file that I need to remove all rows if column A is not equal to 2. I have tried multiple ways in my macro with the latest being:

ActiveSheet.Paste
Sheets("Questions - Status Not 2 digits").Select
ActiveSheet.Range("$A$1:$L$300000").AutoFilter Field:=1, Criteria1:<>"2"
, Operator:=xlFilterValues

Which gives me an error. can this be done? If so, please help!

Thanks so much!

 

[AlphaFrog]

Try using this as the autofilter criteria...

Criteria1:="<>2"

 

[rallcorn]

Try this on a COPY

Sub DeleteRows()
    Dim UnusedColumn As Long, LastRow As Long
    
    LastRow = Cells.Find(What:="*", searchorder:=xlRows, _
        searchdirection:=xlPrevious, LookIn:=xlValues).Row
    
    UnusedColumn = Cells.Find(What:="*", searchorder:=xlByColumns, _
        searchdirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
    
    With Cells(1, UnusedColumn).Resize(LastRow)
        .FormulaR1C1 = "=if(RC1=2,"""",""X"")"
        .Value = .Value
        On Error Resume Next
        .SpecialCells(xlConstants).EntireRow.Delete
        On Error GoTo 0
    End With
MsgBox "Done!"
End Sub