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
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