VBA to Delete All Rows with a Cell Containing a Date Earlier than x

[AnyaK]

Hello,

I'd really appreciate some help. I have a series of rows, with column C containing only dates. I'd like to be able to easily delete all rows where the cell in C contains a date prior to, say 16/06/2017. I know absolutely no VBA, but have naively tried the below based on posts I found here from 2012:

Sub DeleteMyRows()    'or Button1_Click()Dim Rng As Range, Cell As Range


Set Rng = Range(Range("c1"), Range("c" & Rows.Count).End(xlUp))


For Each Cell In Rng
    
    If Cell > 16 / 6 / 2017 Then
        Cell.EntireRow.Delete
    End If


Next Cell


End Sub

All that happens is that rows where the date has an odd-numbered day disappear, but that's not what I need and I'm terribly confused.

I'd be ever so grateful for some help as a solution would save me many hours of work.

Many thanks,

AnyaK


P.S. I'd be happy to post an example of the table, but won't be able to do that for a few hours as it's not possible from work computers. Apologies

 

[mumps]

Try:

Sub delRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim sDate As String
    sDate = InputBox("Please enter the date.")
    If sDate = "" Then
        MsgBox ("You have not entered a date.")
        Application.ScreenUpdating = True
        Exit Sub
    End If
    Range("C1:C" & LastRow).AutoFilter Field:=1, Criteria1:="<" & sDate
    Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

The macro assumes that there are headers in row 1 and data starts in row 2.

 

[AnyaK]

Hello mumps,

Many thanks for your help.

Unfortunately this code seems to delete all rows except for the header row, regardless of the date. Really sorry, I'm not sure where I'm going wrong in using it.

Try:

Sub delRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim sDate As String
    sDate = InputBox("Please enter the date.")
    If sDate = "" Then
        MsgBox ("You have not entered a date.")
        Application.ScreenUpdating = True
        Exit Sub
    End If
    Range("C1:C" & LastRow).AutoFilter Field:=1, Criteria1:="<" & sDate
    Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

The macro assumes that there are headers in row 1 and data starts in row 2.

Many thanks,

Anyak

 

[mumps]

I tried the macro on a dummy sheet and it worked properly. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.