VBA code required to delete rows with future dates - Urgent

[exceluser9]

Hi Team,

I have a data column J and K with formula =IFERROR(IF(G3<>"",WORKDAY(G3,3),""),"") in column J and =IFERROR(IF(H3<>"",WORKDAY(J3,2),""),"") in column K.

I need a vba code to delete the entire row if column "J" has future date, it should retain current date and past date.

And it should also delete entire row if column "K" has a future date (Retain if it has todays date & past date) only if column "H" contains today's date or past date.

If column J & K is blank please retain record.

Column G	Column H		Column J	Column K
06-08-2019	08-08-2019		09/08/2019	13/08/2019
05-08-2019	07-08-2019		08/08/2019	12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019
07-08-2019			12/08/2019

<colgroup><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

 

[Fluff]

Turn on the macro recorder, filter on column J for future dates & then delete the visible rows.
Turn of the recorder & you have your code.

 

[exceluser9]

Hi Fluff,

What about logic in column K?

Dates will keep on changing, recording didn't work.

Could you help with VBA code.

 

[Fluff]

I don't understand you logic for column K also what do you want to happen for the 2nd row of data you posted?
Please also post the recorded macro & explain why it "didn't work"

 

[exceluser9]

Hi Fluff,

What about logic in column K?

Dates will keep on changing, recording didn't work.

Could you help with VBA code for logic and deletion.

 

[Fluff]

Please read post#4

 

[exceluser9]

Hi Fluff,

Row 2 should be deleted since column K has a future date and there is a past date in column H.

Logic 1 - I need a vba code to delete the entire row if column "J" has future date in row (Retain current date and past date)

Logic 2 - Delete entire row if column "K" has a future date only if a date is found in column "H" row (Retain current date and past date)

Logic 3 - If column J and K is blank do not delete the rows.

Tried recording it didnt work. Below is the code.

Sheets("Delete").Select
Cells.Select
Selection.AutoFilter
Range("J1").Select
ActiveSheet.Range("$A$1:$K$10000").AutoFilter Field:=10, Operator:= _
xlFilterValues, Criteria2:=Array(2, "8/12/2019")
End Sub

Could you help please, i dont think recording would work.

 

[exceluser9]

Below is a example with comments

Column G	Column H		Column J	Column K	Comments
02-08-2019	08-08-2019		07/08/2019	09/08/2019	Do not delete
05-08-2019	07-08-2019		08/08/2019	12/08/2019	Delete
					Do not delete
02-08-2019			07/08/2019		Do not delete
02-08-2019			07/08/2019		Do not delete
02-08-2019			07/08/2019		Do not delete
02-08-2019			07/08/2019		Do not delete
02-08-2019			07/08/2019		Do not delete
07-08-2019			12/08/2019		Delete
02-08-2019			07/08/2019		Do not delete
02-08-2019			07/08/2019		Do not delete
02-08-2019			07/08/2019		Do not delete
02-08-2019			07/08/2019		Do not delete

<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>

 

[Fluff]

Ok, does this correctly filter col J?

Sub exceluser9()
With Sheets("Delete")
   .Range("$A$1:$K$10000").AutoFilter 10, ">" & CLng(Date)
End With
End Sub

 

[exceluser9]

It didn't work.

Can you give me complete code please with deletion as well with all logics I mentioned