Advanced Filtering

[adambc]

Can anyone help me work out why this works fine (512 of 1208 records returned) ...

Sub FilterTest()
   
    ActiveSheet.ListObjects("SharepointUsersTable").Range.AutoFilter Field:=13, _
        Criteria1:="<>"
       
    ActiveSheet.ListObjects("SharepointUsersTable").Range.AutoFilter Field:=1, _
        Criteria1:="FALSE"
   
    ActiveSheet.ListObjects("SharepointUsersTable").Range.AutoFilter Field:=10, _
        Criteria1:="<>"
   
    ActiveSheet.ListObjects("SharepointUsersTable").Range.AutoFilter Field:=33, _
        Criteria1:="="

End Sub

(I know this could be written more elegantly, but I decided to record the macro rather than write it to check if I was doing anything wrong!)

... but this ...

Sub AdvancedFilter()

    Range("SharepointUsersTable[#All]").AdvancedFilter Action:=xlFilterInPlace, _
        CriteriaRange:=Sheets("Sheet1").Range("A1:D2"), Unique:=False

End Sub

... where Sheets("Sheet1").Range("A1:D2") looks like this ...

(I have doubled checked that the table Headers are replicated exactly!)

... returns 0 of 1208 records?

Help please!!!

Thanks ...

 

[adambc]

Can anyone help me work out why this works fine (512 of 1208 records returned) ...

Sub FilterTest()
  
    ActiveSheet.ListObjects("SharepointUsersTable").Range.AutoFilter Field:=13, _
        Criteria1:="<>"
      
    ActiveSheet.ListObjects("SharepointUsersTable").Range.AutoFilter Field:=1, _
        Criteria1:="FALSE"
  
    ActiveSheet.ListObjects("SharepointUsersTable").Range.AutoFilter Field:=10, _
        Criteria1:="<>"
  
    ActiveSheet.ListObjects("SharepointUsersTable").Range.AutoFilter Field:=33, _
        Criteria1:="="

End Sub

(I know this could be written more elegantly, but I decided to record the macro rather than write it to check if I was doing anything wrong!)

... but this ...

Sub AdvancedFilter()

    Range("SharepointUsersTable[#All]").AdvancedFilter Action:=xlFilterInPlace, _
        CriteriaRange:=Sheets("Sheet1").Range("A1:D2"), Unique:=False

End Sub

... where Sheets("Sheet1").Range("A1:D2") looks like this ...

View attachment 90342 (I have doubled checked that the table Headers are replicated exactly!)

... returns 0 of 1208 records?

Help please!!!

Thanks ...

IGNORE!!!

Manual filtering recognises Blanks even though they contain a formula; Advanced Filter doesn't!!!

Need to find a work round (without introducing another (helper) column)!!!

 

[adambc]

IGNORE!!!

Manual filtering recognises Blanks even though they contain a formula; Advanced Filter doesn't!!!

Need to find a work round (without introducing another (helper) column)!!!

This did the trick!