One for u VBA / Macro guys - Making an auto filter on two cells values - via a button

Posted by Phil on June 14, 2001 3:51 AM

Does any1 know how I could make a Button so that when pressed will do an AutoFilter on the cells in collumns A:G. Filtering collumn A so that it only shows the values that are greater or equal to the value in J3 and Less than or equal to the value in J4.

J3 = Start Date
J4 = Finish Date

Cheers

Phil

Posted by Barrie Davidson on June 14, 2001 7:04 AM

Hi Phil, here's two macros. The first applies your filter and the second removes the filter.

Sub Filter_On()
'
' Macro written by Barrie Davidson
'
Dim Last_row As Integer
Dim Lower_criteria As String
Dim Upper_criteria As String

Last_row = Range("A1").End(xlDown).Row
Range("A1:G" & Last_row).Select
Lower_criteria = ">=" & Range("J3").Value
Upper_criteria = "&LT;=" & Range("J4").Value
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Lower_criteria, Operator:=xlAnd, _
Criteria2:=Upper_criteria
Range("A1").Select
End Sub
Sub Filter_Off()
'
' Macro written by Barrie Davidson
'

Selection.AutoFilter Field:=1
Range("A1").Select
End Sub

Regards,
Barrie