Filter and VBA using a dynamic range as a criteria

[jbesclapez]

Hello,

I have this formula :

Selection.AutoFilter Field:=2, Criteria1:="<>" & Worksheets("Temp").Range("B2:B" & LastRowKTemp).Value

I can not make it work. If i hard code the criteria it is fine.
The value of the LastRowKTemp is 7, i checked that; It's fine.

When i use that formula, it is doing something
Selection.AutoFilter Field:=2, Criteria1:="<>" & Worksheets("Temp").Range("B2").Value
But as you can see the Criteria is not the one I want.

Then I started using the advanced filter :

   Range("A30:F171").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:F7"), Unique:=False

And the filter is working so I try to make it dynamic using that :

     Range("A30:F171").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:F" & LastRowKTemp), Unique:=False

And then it is not working.

Please guide me to a solution.

Thanks

 

[jbesclapez]

OK. Forget it... I did not pay attention to the name of the Dynamic Range. It was only a spelling mistake. I Cant believe this amount of time i just spent on that.
Sorry for that... it must be the week end now

 

[jbesclapez]

Wait... no, It is really not working. Here is the line I am using
this one works :

 Sheets("Temp").Range("A30:F171").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:F" & LastRowKPlanTemp), Unique:=False

But not that one :

 Sheets("Temp").Range("A30:F171").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= "<>" & Range("A1:F" & LastRowKPlanTemp).Value, Unique:=False

 

[kevpears]

I am interested to see how this works. Currently I am running a similar situation however cannot seem to get it to work either.

    Dim rngCriteria As Excel.Range, rngValues As Excel.Range, FRows As Integer

    Set rngCriteria = Sheet5.Range("C28")


    Sheet2.Select
    With ActiveSheet
    FRows = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    Set rngValues = Range("A1:N" & FRows)
    Call rngValues.AutoFilter(Field:=10, Criteria1:=Split(Replace$(rngCriteria.Text, " ", ""), ","), Operator:=xlFilterValues)

If I change Set rngValues = Range("A1:N" & FRows) to Set rngValues = Range("A1:N500") then it will work. That however will not work as my range will very.