VBA/Macro to delete rows based on date and quantity

[emifri]

Hello! I am looking for help to create a macro able to delete rows based on multiple criteria from a report:

The first criteria being date - this report is generated as a CSV so the date codes in column G are not in date format (see below sample). As well, since this would be an ongoing report I would like the date factor to be "dynamic". As in, it looks at the todays date (or date when report is run) and removes anything that is less than 1 year old.

The second criteria is sales value - I would like to only see rows that have 0 sales in column M

I have listed in column N whether to keep or delete the row. This column is not part of the report.

Any help or advice is appreciated. Thank you!

 

[JpMakako]

I could write you a code that asks for a cell and delete the entire row if it has a "delete" (preferably a TRUE) value in it and goes all the way down till it finds a blank space. That way you could make the formula as complex as you want to.

 

[emifri]

I could write you a code that asks for a cell and delete the entire row if it has a "delete" (preferably a TRUE) value in it and goes all the way down till it finds a blank space. That way you could make the formula as complex as you want to.

Thanks for your reply, I'm not totally sure I understand what you mean though. Ideally I do not want any additional formulas on the report - I just want to generate it, run the macro and see my results. I am not great with this side of excel so please forgive if I misunderstood.

 

[JpMakako]

Sub DeleteIfTrue()
Dim myRange As Range
'Select the Column you want to check for Condition
Set myRange = Application.InputBox("Select First Cell in column to loop.", Type:=8)

While myRange.Value <> ""
If myRange.Value = True Then 'Condition for deleting is a TRUE Value or Change it to delete, etc
Set myRange = myRange.Offset(1)
myRange.Offset(-1).EntireRow.Delete (xlUp)
Else
Set myRange = myRange.Offset(1)
End If
Wend

End Sub

Save a copy, run the macro. when the Box shows up select cell N2, it will scroll down until an empty cell. In this code It will delete any row in which the value in column N = TRUE, you can change it to If myRange.Value = "delete" and it will work on your example. What I meant is that you can select in column N any condition you want with multiple criteria, as long as the value is right the row will be deleted.

 

[emifri]

Sub DeleteIfTrue()
Dim myRange As Range
'Select the Column you want to check for Condition
Set myRange = Application.InputBox("Select First Cell in column to loop.", Type:=8)

While myRange.Value <> ""
If myRange.Value = True Then 'Condition for deleting is a TRUE Value or Change it to delete, etc
Set myRange = myRange.Offset(1)
myRange.Offset(-1).EntireRow.Delete (xlUp)
Else
Set myRange = myRange.Offset(1)
End If
Wend

End Sub

Save a copy, run the macro. when the Box shows up select cell N2, it will scroll down until an empty cell. In this code It will delete any row in which the value in column N = TRUE, you can change it to If myRange.Value = "delete" and it will work on your example. What I meant is that you can select in column N any condition you want with multiple criteria, as long as the value is right the row will be deleted.

Amazing! That worked perfectly. Thank you so much!