Filtering data based on two criteria's and then deleting those rows

[harvey121]

Hi all,

I'm searching for a macro wherein i can filter up to 5000 rows of data based on two conditions and then delete the filtered rows.

An example of what I'm trying to do is as follows:

A sample of my data looks like this:

ColA ColB ColC
[TABLE="width: 342"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD] [/TD]
[TD]Market[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD="align: right"]31-Jan[/TD]
[TD][/TD]
[TD]sweden[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]25-Nov[/TD]
[TD][/TD]
[TD]us[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3-Jul[/TD]
[TD][/TD]
[TD]us[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]15-Aug[/TD]
[TD][/TD]
[TD]canada[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]17-Aug[/TD]
[TD][/TD]
[TD]canada[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]17-Nov[/TD]
[TD][/TD]
[TD]canada[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]21-Aug[/TD]
[TD][/TD]
[TD]sweden[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]25-Aug[/TD]
[TD][/TD]
[TD]us[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]28-Aug[/TD]
[TD][/TD]
[TD]us[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]28-Oct[/TD]
[TD][/TD]
[TD]us[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]28-Aug[/TD]
[TD][/TD]
[TD]us[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]26-Feb[/TD]
[TD][/TD]
[TD]sweden[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]31-Aug[/TD]
[TD][/TD]
[TD]sweden[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]


Now I want to delete all those rows where the Market is Sweden and date is less than 21st March 2018, us and date less than 16th Oct 2018 canada and date less than 23rd Oct 2018.

PS: I'm getting the dates with the following format: 5/31/2018 11:23:51 PM but using short date over here.

Your help guys is really appreciated on this.

 

[harvey121]

I'm getting TRUE as output.

Yeah formatting and the true value can be, and often is, different with dates. What does:

=ISNUMBER(A2)

produce? With A2 housing a date.

 

[steve the fish]

Ok then. So your dates are all true dates. Where is the data housed? Which cells? You do have headers right? What is the sheet name? Check there are no trailing spaces after the country names also. Format the dates to number with say 2 decimal places and paste the results. Finally what is the exact code you are running now?

 

[harvey121]

Hi Steve,

I had an extra row in between. Removed it and it worked perfectly.

I have one more query, As I need to shift the Markets column(Col2 over here) to column 6 adding more date in between, so what all change should I make to your previous code.

Ok then. So your dates are all true dates. Where is the data housed? Which cells? You do have headers right? What is the sheet name? Check there are no trailing spaces after the country names also. Format the dates to number with say 2 decimal places and paste the results. Finally what is the exact code you are running now?