Formula to replace VBA advanced filter

[uk747]

Hi

If I have VBA below to do an advanced filter. Was wondering how can I use a filter formula instead

I know I can use individual formulas in L2, M2, N2 etc

But I want the formula to account for values in different cells in the criteria F1:j2 which the below code does. I don't know which cells in the criteria will have value it could be 1 of them 2, 3 or even all 4

Sheets("sheet1").Range("A1") _
  .CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("sheet2") _
        .Range("F1:J2"), _
    CopyToRange:=Sheets("sheet2") _
        .Range("L1:P1"), _
    Unique:=False

 

[uk747]

wanted to try and avoid VBA and use a formula instead

 

[Fluff]

You don't need VBA for Advanced filter it's on the Data tab.

 

[uk747]

I know but i wanted to automate it without having to manually do it

 

[Fluff]

I really don't see the point, when all you have to do is click on Reapply or Alt A Y1