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

Alex89

New Member
Joined
May 30, 2019
Messages
34
Hi everyone,

I’m having some trouble here, would love to have some feedback on the code I have so far. I have data that I’m trying to filter based on the start date and end date. The Start and End date have their own columns with the rest of the data 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 column 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.

Thanks in advance!!


Option Explicit
Public Sub DeleteRowsWithAutofilterDates()


Dim wksData As Worksheet
Dim lngLastRow As Long
Dim rngData As Range
Dim StartDate As Long
Dim EndDate As Long
'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", _
Operator:=xlAnd, _
Criteria2:="=>StartDate"

'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



End Sub
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
888
Office Version
2010
Platform
Windows
Declare dates as date
The header row is included in the filter range and you need to be sure there actually is something to delete or it'll error.
try this, it assumes headers to be in row 1
Code:
Option Explicit

Public Sub DeleteRowsWithAutofilterDates()

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

'Set references up-front
Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")
StartDate = ThisWorkbook.Worksheets("Enter Info").Range("C2")
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("G1:H" & lngLastRow)
End With

Application.DisplayAlerts = False

With rngData
    'remove any existing filters
    .AutoFilter
    'Apply the Autofilter
    .AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlFilterValues
    .AutoFilter Field:=2, Criteria1:="<=" & EndDate, Operator:=xlFilterValues
    'Delete the visible rows while keeping the header
        'only if there are rows to delete otherwise it errors
        If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
            .Offset(1).Resize(lngLastRow - 1).Delete
        Else
            MsgBox "There are no records to delete."
        End If
    'remove existing filters
    .AutoFilter
End With

Application.DisplayAlerts = True
End Sub
 

Alex89

New Member
Joined
May 30, 2019
Messages
34
Hi, thank you so much for your help! I really appreciate your time

This is great so far, except it's doing the opposite of what I would like. I apologize for being unclear, my goal is to keep the rows of data within the user-specified range. For example, if the start date is April 1st and the end date is April 31st, i would like to keep all rows containing that data within that period of time.

Another note that I did not mention: there are blanks cells in column G, I would like to delete all rows where there are blank cells in Column G

Thanks in advance!
 

Alex89

New Member
Joined
May 30, 2019
Messages
34
For the blanks cell, I've been playing around with this code in the if/elseif statement

If Columns(1) <> "" Then
.Offset(1).Resize(lngLastRow - 1).Delete
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
888
Office Version
2010
Platform
Windows
Sorry 'bout that, hope I do better next time.
Are you saying that the end of the data doesn't matter, only that it only starts within that period, or is the entire time to be within that period
or what about a data that starts before the specified start and finishes after the specified end, it would have data within the period ?
 
Last edited:

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
I believe if you reverse the ">=" Operator in your first Field and the reverse the "<=" Operator in Field 2 you should get the results you require.

Be sure to test on a copy worksheet.

This will delete the entire row if Column G contains a Blank

Code:
Columns("G").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Alex89

New Member
Joined
May 30, 2019
Messages
34
You're the best!!

"what about a data that starts before the specified start and finishes after the specified end, it would have data within the period ?" <-- this is EXACTLY what I've been struggling with. I'm just trying to keep any row of data that falls within the user specified range. For example, if they put the range April 1st - April 30th but there's data that starts in March 8th and ends in April 1st, I want to keep that row. Or if there's data that starts on April 30th and ends on May 8th, I still would like to keep that data.


 

Alex89

New Member
Joined
May 30, 2019
Messages
34
Also, I tried reversing the "<=" in their respective fields and it would delete the rows of data that fall within the date parameters
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
888
Office Version
2010
Platform
Windows
OK, so the way I see this now is:
1) eliminate rows where G is blank,
2) eliminate rows that end before the specified start
3) eliminate rows that start after the specified end
and everything else will be at least partially within the specified dates

Code:
Option Explicit

Public Sub DeleteRowsWithAutofilterDates_v2()

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

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

Application.ScreenUpdating = False

With wksData
    'remove rows where G is blank
    On Error Resume Next    'incase there aren't any
    .Range("G1", Cells(Rows.Count, "G").End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0         're-enable error notification
    'Identify the last row and use that info to set up the Range
    lngLastRow = .Range("G" & .Rows.Count).End(xlUp).Row
    Set rngData = .Range("G1:H" & lngLastRow)
End With

With rngData
    'remove any existing filters
    .AutoFilter
    
    'Apply the Autofilter for starts AFTER end
    .AutoFilter Field:=1, Criteria1:=">" & EndDate, Operator:=xlFilterValues
    'delete these ones if there are any
        If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
            .Offset(1).Resize(lngLastRow - 1).Delete
        End If
    'remove existing filter
    .AutoFilter
    
    'apply autofilter for ends BEFORE start
    .AutoFilter Field:=2, Criteria1:="<" & StartDate, Operator:=xlFilterValues
    'delete these ones if there are any
        If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
            .Offset(1).Resize(lngLastRow - 1).Delete
        End If
    'remove existing filters
    .AutoFilter
End With

Application.ScreenUpdating = True

End Sub
 

Alex89

New Member
Joined
May 30, 2019
Messages
34
I've been trying to troubleshoot this myself, but now for some reason it isn't deleting the rows with blanks in Column G, but everything else is working exactly the way I would like it to!

Small thing, but was wondering if this is possible - when I run the macro I get a prompt that says "Delete Entire Sheet Row," which makes me click it twice before it deletes anything. Is there a way to just delete the sheet rows automatically?

Thanks so much for your help, you're a God send!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,249
Messages
5,467,521
Members
406,544
Latest member
semoredhawk

This Week's Hot Topics

Top