If hire date (column D) is greater than revision date (column F) then delete row

[bruinsrme]

Hi
I am just getting into VBA and macros. The spread sheet i am working on is full of macros, which I cannot believe how much time it actually saves.

This workbook I am working on is a one time use so doing things doesn't have to be as clean as a sustaining worksheet.

All that I am trying to do is check to see if column D greater than column F and if so delete the row.

What would be awesome if the row is moved to a different sheet.

I appreciate the knowledge sharing and your time.

Scott

[bruinsrme]

the actual file has 1368 rows of data, not sure if that matters.

thank you

[stnkynts]

In your actual workbook is column D and F formatted as a date or is it formatted as general (as in your example workbook). It makes a difference.

[bruinsrme]

I can format both columns as either. the real sheet is formatted as date. If general is easier I can format as such.
thank you
Scott

[stnkynts]

The problem with how it is now is that it is not in a proper format which excel can recognize to compare if one date is past another. Is the real sheet formatted as a proper date? In other words, is it still Jun/25/2013 but has had "date" chosen for it as the format type.

[stnkynts]

Here is a way around it, assuming your hand typed in month is 3 characters long and your day/year is 2 and 4 characters respectively. Note the possible need to change the sheet name in code:

Sub ByDate()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1") 'may need to change this to match data sheet
Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2") 'may need to change this to match archive sheet
Dim LR As Long, icell As Long
Dim myMonth1 As Integer, myMonth2 As Integer
Dim myDate1 As Date, myDate2 As Date

LR = ws1.Range("D" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

For icell = LR To 2 Step -1
    Select Case Left(ws1.Range("D" & icell).Value, 3)
        Case Is = "Jan"
            myMonth1 = 1
        Case Is = "Feb"
            myMonth1 = 2
        Case Is = "Mar"
            myMonth1 = 3
        Case Is = "Apr"
            myMonth1 = 4
        Case Is = "May"
            myMonth1 = 5
        Case Is = "Jun"
            myMonth1 = 6
        Case Is = "July"
            myMonth1 = 7
        Case Is = "Aug"
            myMonth1 = 8
        Case Is = "Sep"
            myMonth1 = 9
        Case Is = "Oct"
            myMonth1 = 10
        Case Is = "Nov"
            myMonth1 = 11
        Case Is = "Dec"
            myMonth1 = 12
    End Select
    Select Case Left(ws1.Range("F" & icell).Value, 3)
        Case Is = "Jan"
            myMonth2 = 1
        Case Is = "Feb"
            myMonth2 = 2
        Case Is = "Mar"
            myMonth2 = 3
        Case Is = "Apr"
            myMonth2 = 4
        Case Is = "May"
            myMonth2 = 5
        Case Is = "Jun"
            myMonth2 = 6
        Case Is = "July"
            myMonth2 = 7
        Case Is = "Aug"
            myMonth2 = 8
        Case Is = "Sep"
            myMonth2 = 9
        Case Is = "Oct"
            myMonth2 = 10
        Case Is = "Nov"
            myMonth2 = 11
        Case Is = "Dec"
            myMonth2 = 12
    End Select
    
    myDate1 = myMonth1 & Right(ws1.Range("D" & icell).Value, 8)
    myDate2 = myMonth2 & Right(ws1.Range("F" & icell).Value, 8)
    
    If myDate1 > myDate2 Then
        ws1.Range("A" & icell).EntireRow.Copy _
            Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        ws1.Range("A" & icell).EntireRow.Delete Shift:=xlUp
    End If
Next icell
        
Application.ScreenUpdating = True

End Sub