PDA

View Full Version : Adding filter criteria



lifeson
02-17-2007, 02:10 AM
First post here
:hi:

How do I add the criteria such as "greater than" or "less than" to the code below?
When i recorded the macro, it put in:
Criteria1:= ">=2"

but I want the "2" to be replaced by the value in the txtUpperlimit text box

I assume that I should be declaring the variables somehow but I have got stuck:banghead:

Private Sub Filter_Output()
Dim upper As Double
Dim lower As Double
upper = txtUpperLimit.Value
lower = txtLowerLimit.Value

Sheets("Data").Select
Application.ScreenUpdating = False
Selection.AutoFilter Field:=8, Criteria1:=upper, Operator:=xlAnd
Selection.AutoFilter Field:=7, Criteria1:=lower, Operator:=xlAnd
Cells.Select
Selection.Copy
Sheets("Copy").Select

Range("A1").Select
ActiveSheet.Paste
Application.ScreenUpdating = True

End Sub

Bob Phillips
02-17-2007, 03:36 AM
Private Sub Filter_Output()

Sheets("Data").Select
Application.ScreenUpdating = False
Selection.AutoFilter Field:=8, Criteria1:=">=" & txtUpperLimit.Value, Operator:=xlAnd
Selection.AutoFilter Field:=7, Criteria1:=">=" & txtLowerLimit.Value, Operator:=xlAnd
Cells.Copy
Sheets("Copy").Select

Range("A1").Select
ActiveSheet.Paste
Application.ScreenUpdating = True

End Sub


But, after filtering, would you not want only the visible data?