excel vba date autofilter

Question

Rather simple question that I seem to be struggling with.

I have 2 columns. column1 has dates in it where each cell is the day after the previous. column 2 occasionally has data in it. i need to apply an auto filter show all dates except the 15th and 26th of the month for column 1 and all blank cells in column2. I can do the column2 filter with the code

ws1.Range(Cells(4, 1), Cells(x, 2)).AutoFilter field:=2, Criteria1:="="

but i cant figure out how to filter column 1.

any help would be appreciated.

Thank you.

Answer 1

i need to apply an auto filter show all dates except the 15th and 26th of the month for column 1

You can achieve what you want using an array of relevant dates.

Logic: Create an array and then use Criteria2 paramenter to filter. For example if you record a macro you will get something like this Criteria2:=Array(2, "1/1/2020", 2, "1/4/2020", 2, "1/6/2020").... First dimension of array is the time period group. 2 is for Days.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lRow As Long, i As Long
    Dim DateArray As Variant
    Dim tmpDateString As String: tmpDateString = "2"

    '~~> Set your worksheet here
    Set ws = Sheet2

    With ws
        '~~> Remove any autofilter
        .AutoFilterMode = False

        '~~> Find the last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Check if there is any data
        If lRow > 1 Then
            '~~> Construct your range
            Set rng = .Range("A1:A" & lRow)

            '~~> Create a string with relevant dates
            For i = 2 To lRow
                Select Case Day(.Range("A" & i).Value)
                Case 15, 16
                Case Else
                    tmpDateString = tmpDateString & "," & .Range("A" & i).Value & ",2"
                End Select
            Next i
        End If

        '~~> Split and store in an array
        tmpDateString = Mid(tmpDateString, 1, Len(tmpDateString) - 2)
        DateArray = Split(tmpDateString, ",")

        '~~> Autofilter using an array
        rng.AutoFilter Field:=1, Criteria2:=DateArray, Operator:=xlFilterValues
    End With
End Sub

In Action

If i could get it to show only 15th and 26th that would work as well as im filtering to delete unused dates. – matthew wilcox 1 hour ago

Swap the Case code in Select Case. Give it a try ;)