Autofilter

[odartey]

Hi all,

I will want to record a macro such that it allows me to use the custom autofilter to set up my criteria. i have gone thr the procedure to no avail.

Can anyone help.............

Thanks

 

[Luke M]

The autofilter uses VB similar to this:

[pre]

Selection.AutoFilter Field:=1, Criteria1:="a"

[/pre]
Check the VB helpfile on AutoFilter for more details.

 

[odartey]

Hi Luke, I am not to good at VB, so if you could further give me a more detailed write up.

Thanks

 

[Luke M]

When you record/write your macro, there should be a line somewhere that looks similar to this:

[pre]

Selection.AutoFilter Field:=1, Criteria1:="=a", Operator:=xlOr, _
Criteria2:="=b"

[/pre]
Which tells the Auotfilter what your 2 criteria are, and what operator to use (xlOr or xlAnd). Through the use of the wildcard character or other symbols, you define what the criteria(s) are. Note that you can hard code, or reference a cell value. If you have a recorded macro, that would help give us a reference as to what you are trying to do.

 

[odartey]

I have tried it and it is ok but not what i actually what to do.

This is what i want to do; I have list of Contract Nos running serially, at a point in time i am required to pull out Cnos say from 45 to 65 to work on them. Using Adv filter will require i set a lot more rows of criteria and that does not make it interestin, and that is why i want use the Custom Filter, so tha the Dialog Box is available to set the criteria...

any help Luke

 

[Luke M]

Here's a complete macro as an example. Asks the user for two inputs.

[pre]

Sub SetFilter()
Dim LowerLimit As Integer
Dim UpperLimit As Integer

Do Until LowerLimit > 0
LowerLimit = InputBox("What is the lower limit?", "Lower Limit")
Loop

Do While UpperLimit < LowerLimit
UpperLimit = InputBox("What is the upper limit?", "Upper Limit")
Loop

'This line has multiple parts.
'Need to first state where the Autofilter begins.
'You also need to tell it which field/column the filter will be on
'The two criteria use the greater than or equal to, and less than
'or equal to symbols.

Range("A1").AutoFilter Field:=1, Criteria1:=">=" & LowerLimit, Operator:=xlAnd, _
Criteria2:="<=" & UpperLimit

End Sub

[/pre]