6
Sub Macro1()
‘Remove all except validated
ActiveSheet.Range("$A$1:$H$5202").AutoFilter field:=8, Criteria1:<>"Validated"

Activesheet.Range("$A$2:$O$99999").SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete

ActiveSheet.ShowAllData
 End sub

How to replace "not equal than" in VBA? <> does not work.

chee seng ng
  • 111
  • 1
  • 1
  • 10

1 Answers1

18

If you want your filter criteria to exclude "Validated", then try changing this line:

ActiveSheet.Range("$A$1:$H$5202").AutoFilter field:=8, Criteria1:<>"Validated"

to

ActiveSheet.Range("$A$1:$H$5202").AutoFilter field:=8, Criteria1:="<>Validated"

Note that the = in Criteria:= doesn't have anything to do with your filter criteria. (It relates to VBA and how you provide an argument to a named parameter.)

chillin
  • 4,391
  • 1
  • 8
  • 8
  • I see. Thanks for your help. – chee seng ng Nov 23 '18 at 01:44
  • How would it be applied in case the criteria itself is a variable? – onit Oct 22 '20 at 13:52
  • 2
    @AntonioSantos, depending on the variable type, you might be able to do something like: `ActiveSheet.Range("$A$1:$H$5202").AutoFilter Field:=8, Criteria1:="<>" & someVariable`. This assumes that there is a variable called `someVariable`. If you need to filter by date, you might need to use `CDbl(someVariable)`, since filtering dates programmatically can be a little awkward if I understand correctly. – chillin Oct 23 '20 at 20:07
  • Worked nicely! Thank you! – onit Oct 24 '20 at 22:28