Advanced filtering using VBA - dynamic criteria

[Steeviee]

Dear all,

I am using some code to do advanced filtering. When a drop down box is used, the resultant choice populates the criteria fields (G2) and advanced filtering is used.

What I want to do is have 4 choices. I then need the criteria range to be rows 1:5. However, if say, the 4th choice is All (i.e. no choice) the criteria range should be rows 1:4.

I have tried assigning a variable b:

If Sheets(3).Range("ven4sel").Value = "All" Then
b = 4
    If Sheets(3).Range("ven3sel").Value = "All" Then
    b = 3
        If Sheets(3).Range("ven2sel").Value = "All" Then
        b = 2
        Else
        b = 3
        End If
    Else
    b = 4
    End If
Else
b = 5
End If
    Range("A12:AU10000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Rows("1:b"), Unique:=False

but it really doesn't like this.

Does anyone have any suggestions of how I could do this, depending on the number of choices made?

Many thanks in advance.

 

[Andrew Poulsom]

It would be:

Rows("1:" & b)

 

[Steeviee]

Perfect. Thank you so much.