Autofilter in Visual Basic

[swilkes]

Using Excel 2007 and a novice VBA user.

I am trying to write some code that will access the Autofilter list. However, I would like the autofilter list to be arbitary so I will not really know what the criteria will be inside the quotation marks of the code below. Is there a simple solution in VBA for such a problem:

The current code lines may be something of the form:

Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$21").AutoFilter Field:=2, Criteria1:= _
"Unknown Entry 1"
ActiveSheet.Range("$A$1:$E$21").AutoFilter Field:=2, Criteria1:= _
"Unknown Entry 2"
......[an so on....]

and where the Criteria text is different in each spreadsheet I would like to run the code on.

Any solutions or thoughts would be welcome.

Cheers

 

[carddard]

Where do you get your criteria from?

 

[swilkes]

Criteria will come form the same columns in each spreadsheet, but the criteria will change form spreadsheet to spreadsheet thats the difficulty I am having at the moment). I suspose I may need to define an array or something like that.

Cheers

 

[carddard]

Untested. Try this:

Sub test()
Dim TheArray

'Create array
With ActiveSheet.Range("$A$1:$E$21")
    ReDim TheArray(1 To .Rows.Count, 1 To 1)
    TheArray = .Value2
End With

For i = LBound(TheArray, 1) To UBound(TheArray, 1)
    ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:=TheArray(i, 1)
    
    'Do what you want here...Like paste data somewhere after autofilter
    
Next i

End Sub

 

[swilkes]

Thanks that is the sort of thing I was chasing. Very elegant I must say.

 

[carddard]

Glad it helped. =)