dynamic filter

[markelly]

Hi All

I have a piece for of code for multiple filters. There's one thing im struggling with.
The macro is going to run each month and there's a filter on month code:

ActiveSheet.Range("$A$1:$A$1296").AutoFilter Field:=1, Criteria1:=Array("1", _
"2", "3", "4"), Operator:=xlFilterValues


the problem i have is that the filter will 'expand' each month. How can i make the criteria dynamic so next month it would be:
Criteria1:=Array("1", _
"2", "3", "4", "5")

i tried to use a string variable but as it was string, it has additional quotation marks: ""1", "2", "3", "4""...no values has been selected by the filter. The same resulted when i used an array of 1, 2, 3, 4) as parameter for the Criteria1

Does anyone has any suggestions how to tackle it?

Thanks

 

[Maheshp]

hi

i suggest first copy unique values from column 1 & then filter each value, try this :

Sub test()
Dim ws2 As Worksheet
Dim Crit As String
Dim i As Long
Sheets("Mainsheet").Select '-----change sheet name
Set ws2 = Worksheets.Add(After:=Sheets(Sheets.Count))
    ws2.Name = "Unique"
 With ws2
Sheets("Mainsheet").UsedRange.Columns(1).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=.Range("A1"), Unique:=True
End With
ActiveSheet.AutoFilterMode = False
For i = 2 To ActiveSheet.UsedRange.Rows.Count
Crit = Sheets("Unique").Cells(i, 1)
Sheets("Mainsheet").Select
ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=Crit
'-------------------------------------------------------------do something
Next i
End Sub

 

[Roger Govier]

Hi

You could either hold the start value in a cell, or calculate it directly from the date and use the AND operator as in the following code

     Dim start As Long
    start = month(Date) - 3


    ActiveSheet.Range("$A$1:$A$1296").AutoFilter Field:=1
    ActiveSheet.Range("$A$1:$A$11296").AutoFilter Field:=1, Criteria1:= _
    ">=" & start, Operator:=xlAnd, Criteria2:="<=" & start + 3

 

[markelly]

Thanks for the suggestion. I will test it. Wouldn't it set the value from the last cell of unique values?

 

[markelly]

Sorry, my previous post was in response to Maheshp.

Thanks Roger. Simple yet brilliant. It will work for me. I was looking for a solution with month() / date() functions.

Thank you