Filter data based on two dates using ADODB

Hi All,

I have written code to select data between two dates.

But unfortunately it is not working when I select 1st date from May and 2nd date from June. If I give both the dates from May, it works.

Please find the attached Macro file and have a look.

Macro is activated on the click of the button in sheet called "Macro"

Thanks in advance.

Regards,
Tejas

Works for me. Whats not working?

@ mike & event.

Does not work for me too. Row 8 of the first sheet is not copied in the new sheet.

You can try this

Sub Macro1()
 Dim rngFilter As Range
 Dim sDate1 As String, sDate2 As String
 
 With Worksheets("macro")
    sDate1 = .Range("b1").Value
    sDate2 = .Range("b2").Value
 End With
 With Worksheets("data")
    .Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
        ">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2
    Set rngFilter = .Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible)
 End With

 If Not rngFilter Is Nothing Then
    With Worksheets("macro")
        rngFilter.Copy .Range("a6")
    End With
 End If
End Sub

@Fortis1991

Ah, you are correct. I'm just on my first cup of coffee and not fully awake. Nice catch.

...@Fortis1991

Ah, you are correct. I'm just on my first cup of coffee and not fully awake. Nice catch.

I can see this Mike! Fotis ..not Fortis

I have 2 questions here.

1) Code is not working for me. Am i doing something wrong?

2) What if we need one more condition. lET'S say that in macro sheet in C1, put another criterion. Let's say number 391.

So result must be data with dates>=Macro!B1 AND <=Macro!B2 and =Macro!C1

So the two yellows rows in first sheet must be the result.

@Fotis1991

I can see this Mike! Fotis ..not Fortis

Must be my Fat fingers... LOL This would include the ID filter. See attached wb.

Sub Macro1()
 Dim rngFilter As Range
 Dim sDate1 As String, sDate2 As String
 Dim sID As String
 
 With Worksheets("macro")
    sDate1 = .Range("b1").Value
    sDate2 = .Range("b2").Value
    sID = .Range("c1").Value
 End With
 With Worksheets("data")
 
    .Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
        ">=" & sDate1, Operator:=xlAnd, Criteria2:="<=" & sDate2
    
    .Range("$A$1").CurrentRegion.AutoFilter Field:=2, Criteria1:=sID
    Set rngFilter = .Range("a2").CurrentRegion.SpecialCells(xlCellTypeVisible)
    
 End With

 If Not rngFilter Is Nothing Then
    With Worksheets("macro")
        .Range("a6:b" & .Cells(6, "b").End(xlDown).Row).ClearContents
        rngFilter.Copy .Range("a6")
    End With
 End If
End Sub

Must be something wrong in my PC. Same result!

Never mind. Just was interesting for me and asked. Don't do effort for this.

Just a quick suggestion try changing

    .Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
        ">=" & sDate1, Operator:=xlBetween, Criteria2:="<=" & sDate2
    
    .Range("$A$1").CurrentRegion.AutoFilter Field:=2, Criteria1:=sID

to

    .Range("A1:B9").AutoFilter Field:=1, Criteria1:= _
        ">=" & sDate1, Operator:=xlBetween, Criteria2:="<=" & sDate2
    
    .Range("A1:B9").AutoFilter Field:=2, Criteria1:=sID

Neither this!