Filter Date

[billandrew]

Trying to filter worksheet on date less than 6/5/2018 using the below code. Perplexed....


Thank you for any help.

Sub DateLessThan()Dim Lastrow As Long, i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
Dim sdate As String
sdate = 6 / 5 / 2018
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
If ws.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
ws.Range("A1:E1").AutoFilter field:=1, Criteria1:="<=" & sdate




End Sub

 

[Marcelo Branco]

Maybe...

Sub DateLessThan()
Dim Lastrow As Long, i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
[COLOR=#0000ff]Dim sdate As Long
sdate = DateSerial(2018, 6, 5) ' sdate = June 5, 2018
[/COLOR]Lastrow = Range("A" & Rows.Count).End(xlUp).Row
If ws.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
ws.Range("A1:E1").AutoFilter field:=1, Criteria1:="<=" & sdate
End Sub

M.

 

[JoeMo]

If the dates in col A are real dates (numbers), try dimensioning sdate as a date.

Dim sdate As Date
sdate = #6/5/2018#

 

[billandrew]

Thank you guys. Both worked great.

Question - Can I use an array for multiple dates....

 

[Marcelo Branco]

Thank you guys. Both worked great.

Question - Can I use an array for multiple dates....

Maybe this helps - see posts 2, 3 and 4
https://www.mrexcel.com/forum/excel...er-multiple-criteria-not-working-numbers.html

M.

 

[billandrew]

Is "MyList" an array of dates? Not sure?

 

[billandrew]

Trying to apply Multiple Dates using the array

Sub FilterDateWithArray()Dim Lastrow As Long, i As Long
Dim ws As Worksheet
Set ws = ActiveSheet


Lastrow = Range("A" & Rows.Count).End(xlUp).Row
If ws.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
ws.Range("A1:E1").AutoFilter field:=1, Criteria1:=Array("6/5/2018", "6/14/2018", "5/5/2018")
End Sub

 

[Marcelo Branco]

An alternative is to use Advanced Filter with a formula as criteria - something like

Sub AdvFilter()
    'Range Mylist contains dates
    Dim Lastrow As Long, ws As Worksheet

    Set ws = ActiveSheet
    With ws
        'Clear filter
        .AutoFilterMode = False
        'Cell above criteria formula should be blank
        .Range("G1") = ""
        'Insert criteria (formula) in G2
        .Range("G2").Formula = "=ISNUMBER(MATCH(A2,MyList,0))"
        Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:E" & Lastrow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            .Range("G1:G2"), Unique:=False
    End With
End Sub

M.

 

[billandrew]

I would need to define array (Mylist) to Column A where the dates are located?

 

[Marcelo Branco]

I would need to define array (Mylist) to Column A where the dates are located?

No. MyList is a named range that contains the dates that you want to be displayed after filter, for example (gray area)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

MyList​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD="bgcolor: #BFBFBF"]

06/05/2018​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD="bgcolor: #BFBFBF"]

06/14/2018​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD="bgcolor: #BFBFBF"]

05/05/2018​

[/TD]
[/TR]
</tbody>[/TABLE]

M.