I am modifying this code, Macro - delete rows based on date.
Sub DeleteDateWithAutoFilter()
Dim MySheet As Worksheet, MyRange As Range
Dim LastRow As Long, LastCol As Long
'turn off alerts
Application.DisplayAlerts = False
'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet3")
'identify the last row in column A and the last col in row 1
'then assign a range to contain the full data "block"
With MySheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Column
Set MyRange = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With
'apply autofilter to the range showing only dates
'older than january 1st, 2013, then deleting
'all the visible rows except the header
With MyRange
.AutoFilter Field:=1, Criteria1:="<8/5/2021"
.SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count).Rows.Delete
End With
'turn off autofilter safely
With MySheet
.AutoFilterMode = False
If .FilterMode = True Then
.ShowAllData
End If
End With
'turn alerts back on
Application.DisplayAlerts = True
End Sub
So far I have added an input box where the user can define the date they are looking for. This is working great.
Sub EditedCode()
Dim MySheet As Worksheet, MyRange As Range
Dim LastRow As Long, LastCol As Long
Dim myValue As String
'turn off alerts
Application.DisplayAlerts = False
'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet13")
myValue = InputBox("Enter Date in XX/XX/XXXX Format", "Date Selection", " ")
Range("Y1") = myValue
From here I want to autofilter just the input date to then use .SpecialCells to delete everything but the selected date. I've tried using criteria1/Operator/Criteria2 but I cannot get this to work. Anyone know what I am doing wrong?
Current Full Code:
Sub EditedCode()
Dim MySheet As Worksheet, MyRange As Range
Dim LastRow As Long, LastCol As Long
Dim myValue As String
'turn off alerts
Application.DisplayAlerts = False
'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet13")
myValue = InputBox("Enter Date in XX/XX/XXXX Format", "Date Selection", " ")
Range("Y1") = myValue
'identify the last row in column A and the last col in row 1
'then assign a range to contain the full data "block"
With MySheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Column
Set MyRange = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With
'apply autofilter to the range showing only dates using above input
'older than inputed date, then deleting
'all the visible rows except the header
With MyRange
.AutoFilter Field:=1, Criteria1:="=& myValue"
.SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count).Rows.Delete
End With
'turn off autofilter safely
With MySheet
.AutoFilterMode = False
If .FilterMode = True Then
.ShowAllData
End If
End With
'turn alerts back on
Application.DisplayAlerts = True
End Sub
I have also tried changing the type of myValue and using different funtions on autofilter (<,>,=). Any help is appreciated. Thank you!