VBA: Delete rows based on <= Current month or 15 less than today's date.

[Lres81715]

Hello All,

Background: I've been using this bit of existing code to filter out and delete all the rows I don't need. It's a tad clunky bit of code but works amazingly well and no complaints.

   Dim iRow    As Long   
   For iRow = 10000 To 2 Step -1
      If Cells(iRow, "A") <> "Nation*" And Cells(iRow, "G") <> "U" Then
         Rows(iRow).Delete
      End If
   Next iRow

Thanks MrExcel forums for this bit of modified code.

What I want to do now is add a 3rd criteria to the code (new code in brick color)
If Cells(iRow, "A") <> "Nation*" And Cells(iRow, "G") <> "U" And Cells(iRow, "AI") >Greater than or = to "Current Month" And "if Date in AI is 15 days less than todays date but only if Today falls within first 15 days of the month)" Then

A bit complicated to explain but here's an Example of what I'm talking about.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Today's Date[/TD]
[TD]Column AI[/TD]
[TD]Delete Row?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2015[/TD]
[TD]9/18/2015[/TD]
[TD]No[/TD]
[TD]>= 15 days < than today[/TD]
[/TR]
[TR]
[TD]10/1/2015[/TD]
[TD]10/16/2015[/TD]
[TD]No[/TD]
[TD]w/in current month or >[/TD]
[/TR]
[TR]
[TD]10/10/2015[/TD]
[TD]9/18/2015[/TD]
[TD]Yes[/TD]
[TD]< 15 days < than today[/TD]
[/TR]
[TR]
[TD]10/14/2015[/TD]
[TD]9/30/2015[/TD]
[TD]No[/TD]
[TD]>= 15 days < than today[/TD]
[/TR]
[TR]
[TD]10/15/2015[/TD]
[TD]10/1/2015[/TD]
[TD]No[/TD]
[TD]w/in current month or >[/TD]
[/TR]
[TR]
[TD]10/15/2015[/TD]
[TD]9/30/2015[/TD]
[TD]Yes[/TD]
[TD]<= 15 days < than today[/TD]
[/TR]
[TR]
[TD]10/20/2015[/TD]
[TD]10/1/2015[/TD]
[TD]No[/TD]
[TD]w/in current month or >[/TD]
[/TR]
[TR]
[TD]10/28/2015[/TD]
[TD]11/7/2015[/TD]
[TD]No[/TD]
[TD]w/in current month or >[/TD]
[/TR]
</tbody>[/TABLE]

Long story short. Need to establish Today's date, if column AI has date in same month or greater, don't delete that row.
If Today's Date falls within first 15 days of current month, subtract 15 days from today's date and delete rows that are less than that as in example (Today = 10/10/2015 and AI = 9/18/2015)

Thanks in advance for your help

 

[Lres81715]

Well, ok I just realized that my code that normally works for my other sheets won't work for this one.

   Dim iRow    As Long   
   For iRow = 10000 To 2 Step -1
      If Cells(iRow, "A") <> "Nation*" And Cells(iRow, "G") <> "U" Then
         Rows(iRow).Delete
      End If
   Next iRow

Doesn't work because it's returning back other Names in column A that have U status... it's also including Nation* with other Letters other than U in Column G.

Making a new post on this.