VBA Autofilter between date range and include blanks

[choulee]

Hi,

I have a date column at column 16. I want to filter between July 1, 2022 and July 31, 2022 and include all the blanks, as well. My code lines below didn't seem to work for me. If you can help, I will be very thankful.

Sub function1()

startdate = #7/1/2022#
enddate = #7/31/2022#

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Test")

sh.UsedRange.AutoFilter 16, "=", xlOr, "<=" & enddate
sh.UsedRange.AutoFilter 16, ">=" & startdate, xlOr, "="

End Sub

 

[kevin9999]

Dates are notoriously tricky with Autofilter. See if the following gives you what you want.

Option Explicit
Sub choulee()
    Dim sh As Worksheet
    Set sh = Worksheets("Test")
    Dim startdate As Date
    Dim enddate As Date
    
    startdate = Format("7/1/2022", "m/d/yyyy")
    enddate = Format("7/31/2022", "m/d/yyyy")
    
    With sh.UsedRange
        .AutoFilter 16, ">=" & CLng(startdate), 1, "<=" & CLng(enddate)
    End With

End Sub

 

[kevin9999]

This second method should pick up blank cells as well.

Option Explicit
Sub choulee_2()
    Dim lRow As Long, i As Long, c As Range, arr() As Variant
    Dim sh As Worksheet
    Set sh = Worksheets("Test")
    
    Dim startdate As Date
    Dim enddate As Date
    startdate = Format("7/1/2022", "m/d/yyyy")
    enddate = Format("7/31/2022", "m/d/yyyy")

    lRow = Cells.Find("*", , xlFormulas, , 1, 2).Row
    If lRow = 1 Then lRow = 2
    For Each c In sh.Range("P2:P" & lRow)
        If c = "" Or (CLng(c) >= CLng(startdate) And CLng(c) <= CLng(enddate)) Then
            ReDim Preserve arr(i)
            arr(i) = Format(c, "m/d/yyyy")
            i = i + 1
        End If
    Next c

    With sh.Range("A1").CurrentRegion
        .AutoFilter 16, Array(arr), 7
    End With

End Sub