excel - Trouble filtering out rows on one worksheet based on an array with values from another worksheet in VBA -

my intention have following code compile data "low cpm 1" worksheet array , filter active worksheet based on array. while macro seem affect filters, none of values filtered out. on matter appreciated

  sub macro1()  dim cpm1array(0 300) variant  = 2 ubound(cpm1array)     cpm1array(i) = sheets("low cpm 1").cells(i, 2).value next      activesheet.range("$a$1:$h$251").autofilter field:=3, criteria1:=("<>1 ubound(cpm1array)"), operator:=xlfiltervalues  end sub 

there no simple way autofilter achieve want. cannot use criteria1:="<>myarray"

alternative

1. we know values not want. can find out values of relevant column
2. simply store values of relevant column in array , remove unnecessary values comparing array has values not want.
3. remove blank cells array
4. pass final array autofilter.

in action

let's our worksheet looks shown in below image. taking example of 15 rows.

code

sub sample()     dim ws worksheet     dim myar(1 5) string     dim tmpar variant, arfinal() string     dim lrow long      redim arfinal(0 0)      set ws = activesheet      '~~> creating array of values not want     = 1 5         myar(i) =     next      ws         '~~> last row of col c sice filter on 3rd column         lrow = .range("c" & .rows.count).end(xlup).row          '~~> storing values form c in array         tmpar = .range("c2:c" & lrow).value          '~~> compare , remove values not want         = 1 lrow - 1             j = 1 ubound(myar)                 if tmpar(i, 1) = myar(j) tmpar(i, 1) = ""             next j         next          '~~> remove blank cells array copying them new array         = lbound(tmpar) ubound(tmpar)             if tmpar(i, 1) <> ""                 arfinal(ubound(arfinal)) = tmpar(i, 1)                 redim preserve arfinal(0 ubound(arfinal) + 1)             end if         next          '~~> filter on values want. change range applicable         .range("$a$1:$h$15").autofilter field:=3, criteria1:=arfinal, operator:=xlfiltervalues     end end sub 

output