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?

 

[Ark68]

Yeah!!! Perfect.
So, I assume then this replaces the advance filter concept I was using?
I will have to do some adaptation, but I think I think I'll be able transition.

 

[DanteAmor]

It is right. im glad to help you. thanks for the feedback.