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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Status
Not open for further replies.

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top