excel - VBA Loop Worksheet Filter Using Multiple Variables -

i trying provide easy search tab of employees in order them filter through large data table specific entries , copy row master list , past search tab.

the code below works , copies data entries match, unfortunately "country" criteria works , rest nothing.

what trying tweak code have written criteria work , if criteria blank in corresponding search ignores criteria allowing value in cell copy , pasted. thinking adding if else statements of criteria job i'm not sure how add if else statement in vba strings , tell ignore criteria if blank.

sub search_and_extract_multicriteria()    dim datasheet worksheet 'where data copied  dim reportsheet worksheet 'where data pasted  dim country string  dim subtype string  dim productname string  dim productformula string  dim source string  dim rating string  dim finalrow integer  dim integer 'row counter    set datasheet = sheet1  set reportsheet = sheet3  country = reportsheet.range("a3").value  subtype = reportsheet.range("c3").value  productname = reportsheet.range("d3").value  productformula = reportsheet.range("e3").value  source = reportsheet.range("f3").value  rating = reportsheet.range("g3").value    reportsheet.range("a16:k500").clearcontents     datasheet.select  'finalrow = cells(row.count, 1).end(x1up).row    = 2 500 'finalrow      if cells(i, 1) = country , cells(i, 3) = subtype , cells(i, 4) = productname , cells(i, 5) = productformula , cells(i, 6) = source , cells(i, 2) = testimonialtype , cells(i, 9) = rating           range(cells(i, 1), cells(i, 11)).copy 'copy columns 1 11 (a k)          reportsheet.select           range("a200").end(xlup).offset(1, 0).pastespecial xlpastevaluesandnumberformats           datasheet.select           end if  next    reportsheet.select 'this report sheet selected when procedure ends    end sub

instead of loop, how using built in autofilter method after checking variable populated actual value.

with datasheet.range(.range("a1"), .cells(500, 11))      if len(country) .autofilter 1, country     if len(testimonialtype) .autofilter 2, testimonialtype     if len(subtype) .autofilter 3, subtype     if len(productname) .autofilter 4, productname     if len(productformula) .autofilter 5, productformula     if len(source) .autofilter 6, source     if len(rating) .autofilter 9, rating      .offset(1).specialcells(xlcelltypevisible).copy 'offset 1 remove header row     reportsheet.range("a200").end(xlup).offset(1).pastespecial xlpastevaluesandnumberformats      .autofilter 'reset filter  end