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.

[TABLE="width: 636"]
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Column G[/TD]
[TD]Column H[/TD]
[TD] [/TD]
[TD]Column J[/TD]
[TD]Column K[/TD]
[/TR]
[TR]
[TD]06-08-2019[/TD]
[TD]08-08-2019[/TD]
[TD] [/TD]
[TD]09/08/2019[/TD]
[TD]13/08/2019[/TD]
[/TR]
[TR]
[TD]05-08-2019[/TD]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD]08/08/2019[/TD]
[TD]12/08/2019[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 729"]
<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]Column G[/TD]
[TD]Column H[/TD]
[TD] [/TD]
[TD]Column J[/TD]
[TD]Column K[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD]08-08-2019[/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD]09/08/2019[/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]05-08-2019[/TD]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD]08/08/2019[/TD]
[TD]12/08/2019[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]07-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]12/08/2019[/TD]
[TD] [/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
[TR]
[TD]02-08-2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07/08/2019[/TD]
[TD] [/TD]
[TD]Do not delete[/TD]
[/TR]
</tbody>[/TABLE]

 

[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