Advanced Filter Coming Up Empty Despite Values Existing

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I hope this snippit of my code below is enough to isolate the problem I'm having

Rich (BB code):
           If wpe > 0 Then
                With ws_data
                    If .FilterMode And Not .AutoFilterMode Then
                        .ShowAllData
                    End If
                    ws_vh.Range("F48") = "Waterloo Park"
                    ws_vh.Range("G48") = "WPE"
                    Set rng_af1_criteria = ws_vh.Range("F47:G48")

                    .Range("A:EL").AdvancedFilter _
                        Action:=xlFilterInPlace, _
                        CriteriaRange:=rng_af1_criteria
                    wpemin = WorksheetFunction.Subtotal(105, .Range("N:N"))
                    wpemax = WorksheetFunction.Subtotal(104, .Range("O:O"))
                    If .FilterMode And Not .AutoFilterMode Then
                        .ShowAllData
                    End If
                End With
            End If
            If wpw > 0 Then
                With ws_data
                    If .FilterMode And Not .AutoFilterMode Then
                        .ShowAllData
                    End If
                    ws_vh.Range("F48") = "Waterloo Park"
                    ws_vh.Range("G48") = "WPW"
                    Set rng_af1_criteria = ws_vh.Range("F47:G48")

                    .Range("A:EL").AdvancedFilter _
                        Action:=xlFilterInPlace, _
                        CriteriaRange:=rng_af1_criteria
                    wpwmin = WorksheetFunction.Subtotal(105, .Range("N:N"))
                    wpwmax = WorksheetFunction.Subtotal(104, .Range("O:O"))
                    If .FilterMode And Not .AutoFilterMode Then
                        .ShowAllData
                    End If
                End With
            End If

This code is intended to filter my database (ws_data) using the criteria at worksheet ws_vh.range("F47:G48"). Once the list has been filtered, code is in place to get the minimum and maximum values (times) from columns N and O respectively from worksheet ws_data.

The filter is coming up empty (despite the values of filter existing in both criteria of headers at F and G 47)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Is the criteria to be AND, records with both, OR records with either ?

criteria on same line for AND
separate lines for OR
 
Last edited:
Upvote 0

Book1
FG
47COMPLEXAIR2
48Waterloo ParkWPE
VAR_HOLD


AND ...

Complex = Waterloo Park AND AIR2=WPE

My header row (row 1) is missing a value at column W. It's empty. Would that matter? COMPLEX is column H, and AIR2 is column EL
 
Last edited:
Upvote 0
Anyone have any thoughts? Should my code work as I have it? Should there we something else I should consider? Is there another approach I can take to get the results I seek?
 
Upvote 0
I don't know if what you have should work.

Did you test if the advanced filter works using a new sheet and all headers for criteria ?
Copy the entire ws_data header row to the new sheet and put the specific criteria in the appropriate columns.
Using the new sheets usedrange as rng_af1_criteria should verify if the advanced filter works or not.

If that doesn't help, consider this a bump to hopefully put the question in view of a different audience.
 
Upvote 0
Your code works fine for me as far as the basic filtering is concerned (I removed most of your variables and set up a simple sheet for testing - moved a few things around slightly. The empty column didn't seem to interfere as long as the range to be filtered is defined correctly.


Code:
Sub foo()

    With Worksheets(1)
        If .FilterMode And Not .AutoFilterMode Then
            .ShowAllData
        End If
        Range("EI2") = "Waterloo Park"
        Range("EJ2") = "WPE"
        Set rng_af1_criteria = .Range("EI1:EJ2")
    
        .Range("A:EL").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=rng_af1_criteria
        wpemin = WorksheetFunction.Subtotal(105, .Range("N:N"))
        wpemax = WorksheetFunction.Subtotal(104, .Range("O:O"))
        If .FilterMode And Not .AutoFilterMode Then
            .ShowAllData
        End If
    End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top