Excel advanced filter not returning any data

Question

Trying to get a working filter for quick report building - as seen here.

My code at the moment reads like this:

Option Explicit
Sub GetData()

'Clear data field

    Sheets("Filter").Select
    Range("B10").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear

'Advanced Filter code

    Sheets("RawData").Range("JobRegister[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("RawData").Range("W1:AA2"), CopyToRange:=Sheets("Filter").Range("B10:T10"), Unique:=True



    Range("B10").Select

End Sub

And it only returns the column headers and no results, regardless of what I use as criteria.

My criteria filters look like this:

=IF(Filter!C4="Any","",Filter!C4)

Where C4 contains a DV list drawn from another sheet.

I had it sort of working temporarily (it would filter for some criteria, not others) but I don't know what it was that I did to make it work nor why it's not working any more!

Answer 1

If the code is only returning headers that means you have either one of these issue.

1. You don't have any matching data as per the criteria set in the range Sheets("RawData").Range("W1:AA2"). Manually check for those criteria in the data set and see if you find any matching rows.
2. And if you find some matching rows, there must be an issue with the headers you put in the range W1:AA1 on Sheet RawData. The headers in the criteria range must be exactly same as headers in the data set. Check for any leading or trailing spaces as well.

Edited Code:

Here is the edited code. Please give this a try...

Sub GetData()
Dim sws As Worksheet, dws As Worksheet

Application.ScreenUpdating = False

Set sws = Sheets("RawData")
Set dws = Sheets("Filter")

'Clearing the Criteria Range
sws.Range("W2:AA2").ClearContents

If dws.Range("C3") = "" Or dws.Range("C3").Value = "Any" Then
    sws.Range("X2").Value = ""
Else
    sws.Range("X2").Value = dws.Range("C3").Value
End If

If dws.Range("C4") = "" Or dws.Range("C4").Value = "Any" Then
    sws.Range("Z2").Value = ""
Else
    sws.Range("Z2").Value = dws.Range("C4").Value
End If

If dws.Range("C5") = "" Or dws.Range("C5").Value = "Any" Then
    sws.Range("Y2").Value = ""
Else
    sws.Range("Y2").Value = dws.Range("C5").Value
End If

If dws.Range("C6") = "" Or dws.Range("C6").Value = "Any" Then
    sws.Range("AA2").Value = ""
Else
    sws.Range("AA2").Value = dws.Range("C6").Value
End If

If dws.Range("C7") = "" Or dws.Range("C7").Value = "Any" Then
    sws.Range("W2").Value = ""
Else
    sws.Range("W2").Value = dws.Range("C7").Value
End If


'Clear data field

dws.Range("B10").CurrentRegion.Clear

'Advanced Filter code
sws.Range("JobRegister[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    sws.Range("W1:AA2"), CopyToRange:=dws.Range("B10:T10"), Unique:=True

Application.ScreenUpdating = True
End Sub