Excel: Delete entirerow if cellvalue in column A has "xxxx" as year


Please take me out of my misery. I think I'm going insane from google search overload. I have a workbook with 5000+ entries. It has dates in the format (dd.mm.yy) in Column A. All I need to do is delete ALL the rows that are not between 2005 and 2010 (inclusive both years) For example: (1) 22.02.14 (2) 01.01.07 (3) 02.12.01 So, Rows 1 and 3 would be deleted and 01.01.07 is kept because it is between 2005 and 2010. Please help me, I got over 6 workbooks to do this and really can't do this manually. Below is the code I sort of made. Doesn't work ofcourse but brilliant code for crashing computers. James

Sub DeleteRows()
    
    Dim i As Long
    Dim st As Long
    Dim en As Long
    Dim test As Long
'Set ranges to four digit year code'
    st = 2005
    en = 2010
For i = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    
    test = Val(WorksheetFunction.Text(ActiveSheet.Range("A" & i).Value, "yyyy"))
    If test < st Or test > en Then
    ActiveSheet.Range("A" & i).EntireRow.Delete
    End If
    Next i
    
End Sub


This question generated 25 answers. To proceed to the answers, click here.

This thread is current as of February 24, 2017.


For more resources for Microsoft Excel