Sub DeleteRowPerTests()
'
Dim OldestDate As Date
Dim PreviousRow As Long
Dim LastRowInColumnG As Long
Dim OffsetColumn As Long
Dim OffsetRow As Long
Dim RowToDelete As Long
Dim StartRowData As Long
Dim cell As Range
'
StartRowData = 6
'
LastRowInColumnG = Range("G" & Rows.Count).End(xlUp).Row ' Get last row # used in column G
'
StartOfForEachLoop:
For Each cell In Range("G" & StartRowData & ":G" & LastRowInColumnG) ' Loop through the G column
OffsetColumn = 0
OffsetRow = 0
'
If cell.Value = "Cash Available" And cell.Offset(OffsetRow, OffsetColumn + 1) <> vbNullString And _
cell.Offset(OffsetRow, OffsetColumn + 1) < cell.Offset(OffsetRow - 1, OffsetColumn + 1) And _
cell.Offset(OffsetRow - 2, OffsetColumn - 3) = "Okinawa" _
And cell.Offset(OffsetRow - 2, OffsetColumn - 6) = "OsaCon" Then ' If all conditions are met then ...
'
OldestDate = cell.Offset(OffsetRow - 2, OffsetColumn - 2).Value2 ' Save the OldestDate
RowToDelete = cell.Offset(OffsetRow - 2, OffsetColumn - 2).Row ' Save the RowToDelete for the OldestDate
'
For PreviousRow = 3 To RowToDelete - StartRowData
If cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 3) = "Okinawa" And _
cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 6) = "OsaCon" Then ' If both other conditions are met then ...
If cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Value2 < OldestDate Then ' If older date found then ...
OldestDate = cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Value2 ' Save the Date as the OldestDate
RowToDelete = cell.Offset(OffsetRow - PreviousRow, OffsetColumn - 2).Row ' Save the RowToDelete for the OldestDate
End If
Else
Exit For ' Exit for loop
End If
Next
'
Rows(RowToDelete).EntireRow.Delete ' Delete the row with the earliest date that matches all criteria
'
GoTo StartOfForEachLoop ' Go back to beginning of loop and check again
End If
Next
End Sub