Dynamic range for autofilter criteria.

[Jagdev Singh]

Hi Experts,

I have the below code which works fine and now I am looking to amend it to consider dynamic criteria range.

Sub DynamicFilter()

SearchCol = "AccountsController"
'SearchFor = Array(Sheet6.Range("A1:A2"))

Dim rng1 As Range
Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=Sheet6.Range("A1:A2") 'SearchFor

End Sub

Please let me know what I am doing wrong here.

Regards,

JD

 

[Jagdev Singh]

Hi Experts,

Any views on the above thread.

Regards
JD

 

[NARAYANK991]

Hi ,

You can get immediate answers by :

1. Explaining your problem in all possible detail ; what do you mean by dynamic criteria range ? You say you want to amend the posted code , which you say is working fine , and then in your next sentence you want to know what you are doing wrong ; do you mean to say the posted code does not work ? Is the posted code before you have changed it , or after ? If a problem is explained clearly , no one will have any doubts on what you want or what the problem is.

2. Uploading a file ; the only reason not to upload a workbook with relevant data / code / objects in it , is if it is confidential and you do not find a way to randomize the data.

Narayan

 

[Jagdev Singh]

Hi Narayan

Thanks for your input and sorry again as usual for the mess-up!

I tweak the code and was trying to make it dynamic. The code works fine for a single criteria and the code for it is mentioned below

Sub DynamicFilter()

SearchCol = "AccountsController"
SearchFor = “Jagdev”

Dim rng1 As Range
Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=SearchFor

End Sub

I was looking to change the SearchFor variable in the above code to a range. Sheet6.range(“A1:A10”) and I think that is not a correct way to do it. My Cretria to autofilter the data is keeps on changing. So is it possible to make it dynamic and filter the sheet accordingly.

Regards,

JD