Advanced Filter Coming Up Empty Despite Values Existing

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,846
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)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,048
Office Version
  1. 2010
Platform
  1. Windows
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:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,846
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">47</td><td style="border-bottom: 1px solid black;background-color: #8DB4E2;;">COMPLEX</td><td style="border-bottom: 1px solid black;background-color: #8DB4E2;;">AIR2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">48</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Waterloo Park</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WPE</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">VAR_HOLD</p><br /><br />

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:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,846
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,048
Office Version
  1. 2010
Platform
  1. Windows
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.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,991
Members
415,872
Latest member
ReignEternal

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
Top