excel - Delete all rows in a column that don't match a value in another worksheet using VBA -

i learning vba , having trouble trying develop macro in workbook 2 worksheets. first worksheet named "para" , second named "report".

when enter number or text para!c6 (e.g. 76894), vba code delete rows in column report!y:y except 1 matches value in para!c6.

i tried following code, not working:

sub keeponlyatsymbolrows()     dim ws worksheet     dim rng range     dim lastrow long     dim s integer      s = worksheets("para").range("c6")      set ws = activeworkbook.sheets("report")      lastrow = ws.range("y" & ws.rows.count).end(xlup).row      set rng = ws.range("a1:ar" & lastrow)      ' filter , delete header row     rng         .autofilter field:=1, criteria1:="<>*s*"         .offset(1, 0).specialcells(xlcelltypevisible).entirerow.delete     end      ' turn off filters     ws.autofiltermode = false end sub 

try code below.

if rng column "a" column "ar", , want filter according column "y", need filter field:=25 (not field:=1 , column a).

also, filter according value of variable, need take variable name s outside ", criteria1:="<>" & s.

code

option explicit  sub keeponlyatsymbolrows()  dim ws          worksheet dim rng         range dim lastrow     long dim s           variant  s = worksheets("para").range("c6").value  set ws = activeworkbook.sheets("report")     ws     lastrow = .range("y" & .rows.count).end(xlup).row     set rng = .range("a1:ar" & lastrow) end  ' filter , delete header row rng     .autofilter field:=25, criteria1:="<>" & s, operator:=xland     .offset(1, 0).specialcells(xlcelltypevisible).entirerow.delete end  ' turn off filters ws.autofiltermode = false  end sub