Auto Filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used a Input Form which collects Date Range. In execute event I have
written following code

Selection.AutoFilter Field:=20,
Criteria1:=">=" & Txt_From,
Operator:=xlAnd,
Criteria2:="<=" & Txt_To

I dont find any mistake in the above code. Data is entering in the custom
filter. But filter is not doing its work. Further Date format which I Enter
is DD/MM/YY.

I have applied filter on several other fileds and it works. But filter on
Date fields doesnot work.

Please send your valuable solution.

Regards

Grisha Jose
 
Try converting the dates to serial numbers. For example:

Selection.AutoFilter Field:=20, _
Criteria1:=">=" & CLng(Txt_From), _
Operator:=xlAnd, Criteria2:="<=" & CLng(Txt_To)

Dim strStart As String
Dim strEnd As String
strStart = Application.InputBox("Start Date")
strEnd = Application.InputBox("End Date")

Selection.AutoFilter Field:=1, _
Criteria1:=">=" & CLng(CDate(strStart)), Operator:=xlAnd, _
Criteria2:="<=" & CLng(CDate(strEnd))
 
Debra Dalgleish said:
Try converting the dates to serial numbers. For example:

Selection.AutoFilter Field:=20, _
Criteria1:=">=" & CLng(Txt_From), _
Operator:=xlAnd, Criteria2:="<=" & CLng(Txt_To)

Dim strStart As String
Dim strEnd As String
strStart = Application.InputBox("Start Date")
strEnd = Application.InputBox("End Date")

Selection.AutoFilter Field:=1, _
Criteria1:=">=" & CLng(CDate(strStart)), Operator:=xlAnd, _
Criteria2:="<=" & CLng(CDate(strEnd))
Click to expand...

Hi:

I have written an Auto filter program in VBA for a work sheet for name
of people and their countries, which is controlled by button click,
and is running quite successfully. However I would like to have the
results of the program to be shown / thrown on separate sheet rather
than the same database sheet, and would like to print those result.

Will any one help me here? .

Thanks – Raj
 
The following code will copy the filtered range to Sheet2. You can
record the steps as you print the sheet, and copy the resulting code to
your macro:

'=============================
Sub CopyFilter()
'copy data and headings from filtered table
Dim rng As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

On Error Resume Next
Set rng = ws1.AutoFilter.Range
On Error GoTo 0
If rng Is Nothing Then
MsgBox "No data to copy"
Else
ws2.Cells.Clear
rng.Copy Destination:=ws2.Range("A1")
End If

End Sub
'============================
 
Back
Top