How to filter and delete rows of data based on user-specified date?

Question

I have data to filter based on the start date and end date.

The Start and End date have their own columns in, "Master Publisher Content." Start date is in column G and End date is in column H.

I'm trying to filter and delete the rows of data based on a user-specified date range on the "Enter Info" tab, the user-specified start date is in cell C2 and the user-specified end date is in cell E2.

For example, if cell C2 is April 1st, 2019 and cell E2 is April 30th, 2019 I want to delete the rows of data that are not between these dates.

This code is just working with the end date. It says no cells were found.

Public Sub DeleteRowsWithAutofilterDates()

    Dim wksData As Worksheet
    Dim lngLastRow As Long
    Dim rngData As Range
    Dim StartDate As Range
    Dim EndDate As Range

    'Set references up-front
    Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")
    Set StartDate = ThisWorkbook.Worksheets("Enter Info").Range("C2")
    Set EndDate = ThisWorkbook.Worksheets("Enter Info").Range("E2")

    'Identify the last row and use that info to set up the Range
    With wksData
        lngLastRow = .Range("G" & .Rows.Count).End(xlUp).Row
        Set rngData = .Range("G2:G" & lngLastRow)
    End With

    '------------------------------------------------------------
    Application.DisplayAlerts = False
    With rngData
        'Apply the Autofilter method to the first column of
        .AutoFilter Field:=1, Criteria1:="<=EndDate"

        'Delete the visible rows while keeping the header
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
    End With

    '----------------------------------------------------------------

    Application.DisplayAlerts = True

    'Turn off the AutoFilter
    With wksData
        .AutoFilterMode = False
        If .FilterMode = True Then
            .ShowAllData
        End If
    End With

    'Let the user know the rows have been removed
    MsgBox "Damn son! Rows removed."

End Sub

Warcupine · Accepted Answer · 2019-06-25 16:35:54Z

1

You are looking for the value of the cell's relating to the start and end date, not the range itself. take Dim StartDate As Range and Dim EndDate As Range and make them Dim StartDate As Date and Dim EndDate As Date.

After that change Set StartDate = ThisWorkbook.Worksheets("Enter Info").Range("C2") to StartDate = ThisWorkbook.Worksheets("Enter Info").cells(2, "C").value And Set EndDate = ThisWorkbook.Worksheets("Enter Info").Range("E2") to EndDate = ThisWorkbook.Worksheets("Enter Info").cells(2, "E").value

This will get you the value of the cell instead of its range.

lngLastRow = .Range("G" & .Rows.Count).End(xlUp).Row Should be lngLastRow = .Cells(.Rows.Count, "G").End(xlUp).row

I'm not sure how many rows you will be going through but actually deleting can be pretty slow if you have a lot, so this will clear the row and then sort.

    Dim wksData As Worksheet
    Dim lngLastRow As Long
    Dim LastCol As Integer
    Dim StartDate As Date
    Dim EndDate As Date

    Dim tDate As Date
    Dim iter As Long

    Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")

    StartDate = ThisWorkbook.Worksheets("Enter Info").Cells(2, "C").value
    EndDate = ThisWorkbook.Worksheets("Enter Info").Cells(2, "E").value

    With wksData
        lngLastRow = .Cells(.Rows.Count, "G").End(xlUp).row

        For iter = 2 To lngLastRow
            if .Cells(iter, "G").value = "" or isnull(.Cells(iter, "G").value) then
                .Rows(iter) = ""
            else
                tDate = .Cells(iter, "G").value

                If tDate < StartDate Or tDate > EndDate Then
                    .Rows(iter) = ""
                End If
            end if
        Next
        LastCol = .Cells.Find(What:="*", after:=ActiveSheet.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        .Range(Cells(1, 1), Cells(lngLastRow, LastCol)).Sort key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
    End With

edited Jun 25, 2019 at 16:35

answered Jun 21, 2019 at 13:10

Warcupine

4,7203 gold badges18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alex Over a year ago

thanks so much for your time, I appreciate it! I seem to be getting a "Type Mismatch error" from the following line of code: tDate = .Cells(iter, "G").Value

Alex Over a year ago

Could it be the way, the date is formatted? So far i've tried DD/MM/YYYY and MM/DD/YYYY

Warcupine Over a year ago

Do all the cells in column "G" have values that are dates? An empty cell or a string not formatted as a date would throw an error. The actual date format should not matter you could do MM/DD/YYYY, DD-MM-YY, etc. it should have no problem with those.

Alex Over a year ago

Some cells in Column G are blank, I would like to delete the rows where the cells are blank. All of the cells in column G and H are formatted as MM/DD/YYYY but appear as "Month Day, Year"

Warcupine Over a year ago

@Alex Just edited in a check for blanks, that should stop the error.

Collectives™ on Stack Overflow

How to filter and delete rows of data based on user-specified date?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related