Unanticipated Results With Advanced Filter

[Ark68]

Hello all,

I have a worksheet ("ws_schedule") with column A populated with a series of values of "ref", "fcn", "fac", "x" or "x2". These values help the filtering process needed to create unique lists for a userform listbox.

The user can choose which series ("ref", "fcn", "fac", "x" or "x2") to view in the listbox.
if the user wants to view only "fac" then variable cn_t = 1; to view only "x" then variable cn_t = 2; to view only "ref" then variable cn_t = 3; to view only "fcn" then variable cn_t = 4; to view only "x2" then variable cn_t = 5; or to view ALL in the listbox, cn_t=6

I use the vba advanced filter function to do this, but it's not giving me the results I'm anticipating. I suspect I don't have a firm enough grasp on advanced filters to make it work so I'm here looking for some guidance.

Here is my code:

With ws_schedule
        .AutoFilterMode = False
        'define filter criteria
        cn_t = red_flav
        If cn_t = 1 Then 'fac red fac
            cn_s = 3
            cn_e = 3
            rn_e = 5
        ElseIf cn_t = 2 Then 'xfac red
            cn_e = 4
            cn_e = 4
            rn_e = 6
        ElseIf cn_t = 3 Then ' ref red
            cn_s = 4
            cn_e = 1
            rn_e = 3
        ElseIf cn_t = 4 Then 'fcn red
            cn_s = 2
            cn_e = 2
            rn_e = 4
        ElseIf cn_t = 5 Then 'classc red
            cn_s = 5
            cn_e = 5
            rn_e = 7
        Else                 'all red
            cn_s = 5
            cn_e = 5
            rn_e = 7
        End If
        
        .Range("A:R").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws_vhold.Range(ws_vhold.Cells(2, cn_s), ws_vhold.Cells(rn_e, cn_e))
        
        Set RngList = .Range("A1:R" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible) 'visible rows of the filtered range
        
        ws_temp1.Cells.ClearContents 'clear temp holding range
        RngList.Copy ws_temp1.Range("A1") 'copy filtered data to temp holding range
End With

Once the data is filtered, the filtered data is copied to a second worksheet (ws_temp1).

Here is my CriteriaRange

Book1
	A	B	C	D	E
2	REC_ID	REC_ID	REC_ID	REC_ID	REC_ID
3	=ref
4		=fnc
5			=fac
6				=x
7					=x2
VHold

If I choose to view "x2" for example, the filter is simply REC_ID = X2. The result is all rows being displayed, in other words, no filter was applied. Where have I gone wrong?

 

[DanteAmor]

Try this:

Criteria:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:77.94px;" /><col style="width:77.94px;" /><col style="width:77.94px;" /><col style="width:77.94px;" /><col style="width:77.94px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">REC_ID</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">REC_ID</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">REC_ID</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">REC_ID</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">REC_ID</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >=REF</td><td >=fnc</td><td >=fac</td><td >=x</td><td >=X2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

.Range("A:R").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws_vhold.Range(ws_vhold.Cells(2, cn_s), ws_vhold.Cells([B][COLOR=#ff0000]3[/COLOR][/B], cn_e))

 

[Ark68]

Thank you Dante! For the most part this worked. One option for user is to see all the records. With your method, I am only getting a blank filtered result.

        ...
        Else                 'all red
            cn_s = 1
            cn_e = 5
        End If
        
        .Range("A:R").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws_vhold.Range(ws_vhold.Cells(2, cn_s), ws_vhold.Cells(3, cn_e))
        
        Set RngList = .Range("A1:R" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)

Book1
	A	B	C	D	E
2	REC_ID	REC_ID	REC_ID	REC_ID	REC_ID
3	=ref	=fnc	=fac	=x	=x2
VHold

 

[Ark68]

Is anyone able to advise how I can filter all these with an advanced filter?

 

[DanteAmor]

It works with my tests.
You can put an example of your data or You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Ark68]

Thank you Dante for your support.

I've attached a link to the data file. This file is strictly data. The code that applies the filter, and holds the criteria range is in a second workbook. Unfortunately, I can't include it because you would just get frustrated working around all the errors associated with network paths, and if I tried to censor it it would take forever and doing so would ultimately cause greater errors.

The code refers to this data to filter. Once data is filtered, code copies the filtered results to a holding worksheet (Vhold) in my main workbook where it is used for a listbox source.

All the other individual filters work. Its just when I try to filter for all the options that the filter fails. The filter is applied, but it filters out ALL the data in the worksheet, leaving only the rows from the end of the dataset exposed. (these blank rows all have the blue row number evidence of an applied filter.

https://drive.google.com/file/d/15DYp0cez-ZGaN3o2inRdNqUI2DwZqaHV/view?usp=sharing

Thank you!

 

[DanteAmor]

You mentioned a listbox, then you have a userform?

 

[DanteAmor]

Try the following in my test file:

Private Sub CommandButton1_Click()
    Dim ws_schedule As Worksheet, ws_vhold As Worksheet, temp1 As Worksheet
    Dim RngList As Range
    
    Set ws_schedule = Sheets("schedule")
    Set ws_vhold = Sheets("holds")
    Set ws_temp1 = Sheets("Temp1")
    
    ws_schedule.AutoFilterMode = False
    If ListBox1.ListIndex = -1 Then v = "All" Else v = ListBox1.List(ListBox1.ListIndex)
    Select Case v
        Case "All":     ws_vhold.Range("A3").Value = ""
        Case Else:      ws_vhold.Range("A3").Value = ListBox1.List(ListBox1.ListIndex)
    End Select
    ws_schedule.Range("A:R").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws_vhold.Range("A2:A3")
    'visible rows of the filtered range
    Set RngList = ws_schedule.Range("A1:R" & ws_schedule.Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        
    ws_temp1.Cells.ClearContents        'clear temp holding range
    RngList.Copy ws_temp1.Range("A1")   'copy filtered data to temp holding range
End Sub


Private Sub UserForm_Activate()
    ListBox1.List = Array("ref", "fnc", "fac", "x", "x2", "All")
End Sub

https://www.dropbox.com/s/nxg1ynpxhk7mfd5/Jun-24 (Mon) Data_edit dam.xlsm?dl=0

 

[Ark68]

Thank you so much for the effort Dante, I really do appreciate it.
I may have miscommunicated my intentions. When I select ALL from your list box, the result is indeed ALL, which includes the empty rows (ie no value in A). For ALL, I'm looking for the collection of ref, fcn, fac, x, and X2.

 

[DanteAmor]

But the other filters are fine?
Then only make this small change

Private Sub CommandButton1_Click()
    Dim ws_schedule As Worksheet, ws_vhold As Worksheet, temp1 As Worksheet
    Dim RngList As Range
    
    Set ws_schedule = Sheets("schedule")
    Set ws_vhold = Sheets("holds")
    Set ws_temp1 = Sheets("Temp1")
    
    ws_schedule.AutoFilterMode = False
    If ListBox1.ListIndex = -1 Then v = "All" Else v = ListBox1.List(ListBox1.ListIndex)
    Select Case v
        Case "All":     [COLOR=#0000ff]ws_vhold.Range("A3").Formula = "=""<>"" & """""[/COLOR]
        Case Else:      ws_vhold.Range("A3").Value = ListBox1.List(ListBox1.ListIndex)
    End Select
    ws_schedule.Range("A:R").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws_vhold.Range("A2:A3")
    'visible rows of the filtered range
    Set RngList = ws_schedule.Range("A1:R" & ws_schedule.Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        
    ws_temp1.Cells.ClearContents        'clear temp holding range
    RngList.Copy ws_temp1.Range("A1")   'copy filtered data to temp holding range
End Sub