How to filter/delete rows of data based on user date

Status
Not open for further replies.

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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,099,815
Messages
5,470,953
Members
406,736
Latest member
hassan mohamed

This Week's Hot Topics

Top