Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable in AutoFilter criteria
I want to let people custom filter a list by typing a date into tw
different cells and using greater than or equal to and less than o equal to, to get a range. This will be inside an automated macro s they won't be able to do the filter manually. My code doesn't give m any errors but it filters everything out and doesn't show any records Can someone tell me why my variables won't work in the code liste below? (THE RANGE ON SHEET2 ARE AS FOLLOWS): ("C1") = "01-May-04" ("C2") = "31-May-04" -------------------------------------------------------------------------------- Sub myfilter() Dim begDate As String Dim endDate As String begDate = Sheet2.Range("C1").Value endDate = Sheet2.Range("C2").Value Range("A1:H1").Select Selection.AutoFilter ActiveWindow.SmallScroll ToRight:=3 Selection.AutoFilter Field:=7, Criteria1:="TRUE" Cells.Select Range("C1").Activate Selection.Copy Sheets("Sheet3").Select Range("A1").Select ActiveSheet.Paste Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").ColumnWidth = 57.86 Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").ColumnWidth = 7.29 Columns("H:H").EntireColumn.AutoFit Selection.AutoFilter Field:=5, Criteria1:="=" & "begDate" Operator:=xlAnd _ , Criteria2:="<=" & "endDate" End Sub -------------------------------------------------------------------------------- Thank you for any suggestions Morr -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable in AutoFilter criteria
morry
replace Selection.AutoFilter Field:=5, Criteria1:="=" & "begDate" Operator:=xlAnd _ , Criteria2:="<=" & "endDate" with Selection.AutoFilter Field:=5, Criteria1:="=" & begDate Operator:=xlAnd _ , Criteria2:="<=" & endDate you may also need to dim your variables as dates I would also replace Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").ColumnWidth = 57.86 Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("F:F").ColumnWidth = 7.29 Columns("H:H").EntireColumn.AutoFit with Columns("A:H").EntireColumn.AutoFit Columns("C:C").ColumnWidth = 57.86 Columns("F:F").ColumnWidth = 7.29 and replace Cells.Select Range("C1").Activate Selection.Copy WITH Cells.cop -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple criteria in autofilter | Excel Worksheet Functions | |||
Displaying autofilter criteria | Excel Worksheet Functions | |||
Setting autofilter criteria | Excel Programming | |||
3 Criteria on one column in AutoFilter? | Excel Programming | |||
VBA Autofilter Criteria | Excel Programming |