Delete rows based on multiple criteria (Date & Text)

**Poulan** · 10-06-2010, 06:34 AM

Please Register to Remove these Ads

Hello,

I'm quite a novice here. I'm trying to delete rows where column C contains specific text in some instances. In other cases I am trying to delete rows where specific text is found in column C AND column M contains the year 2009 (in format dd-mm-yyyy).

The code I'm working with so far is as follows:

Please Login or Register  to view this content.

**venkat1926** · 10-06-2010, 07:30 AM

i have slightly modified your macro (see third row from below).see whether it helps
the default property of a range is its value
you can write

Please Login or Register  to view this content.

but it is enough if you write

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Poulan** · 10-08-2010, 04:23 AM

This is what I've got now but I'm getting a run time error 424 and the actual macros is failing to do anything:

Please Login or Register  to view this content.

**Marcol** · 10-08-2010, 04:42 AM

You have not defined (the worksheet?) EntryDate.

This code might be better

Please Login or Register  to view this content.

I suspect that EntryDate is not required, but can't be sure.

Hope this helps

**Poulan** · 10-08-2010, 05:13 AM

Ok, I've removed the EntryDate but now I'm getting a compile error on line 12 with

Please Login or Register  to view this content.

highlighted by the debugger.

Please Login or Register  to view this content.

**Marcol** · 10-08-2010, 05:25 AM

Sorry, my mistake I didn't define LastRow

Try this

Please Login or Register  to view this content.

**Poulan** · 10-08-2010, 05:45 AM

Thanks for looking at this buddy. It ran without errors and successfully deleted the "Germany Domestic" rows but didn't touch the "Intl Corporate" ones which have 2009 in column M. The logic of the macro should mean that it deletes 3 of the 5 "Intl Corporate" entries. I'm not sure why this is not working. I've uploaded the sheet again to help anyone who wants to test it with the macro as I have it with the changes. On "Sheet2" is the original data for pasting back into "Sheet1" for retesting.

**snb** · 10-08-2010, 06:36 AM

Or

Please Login or Register  to view this content.

NB. this code runs only if 'option explicit' has been removed or 'hyphenated'

**Poulan** · 10-08-2010, 06:41 AM

That code ran but didn't delete any of the "Intl Corporate" records from "2009".

**Marcol** · 10-08-2010, 06:48 AM

Try this

Please Login or Register  to view this content.

**Poulan** · 10-08-2010, 06:51 AM

Great, I'm going to try to understand why it didn't work out in the first place. In the mean time: I love you

**Marcol** · 10-08-2010, 06:56 AM

Glad to have helped

Column M contains a Date
Therefore you need

Please Login or Register  to view this content.

to return e.g. 2009

**snb** · 10-08-2010, 07:20 AM

In that case:

Please Login or Register  to view this content.

**Poulan** · 10-08-2010, 08:01 AM

Originally Posted by snb

In that case:

Please Login or Register  to view this content.

I tried that but it gave a "run time error 13, type mismatch" error

**Poulan** · 10-08-2010, 08:05 AM

Originally Posted by Marcol

Glad to have helped

Column M contains a Date
Therefore you need

Please Login or Register  to view this content.

to return e.g. 2009

I've imported your code and used that logic to form part of a slightly larger macro (I wanted to keep it simple for uploading here so that I could get the logic) but it's failing.

The error I am getting is "Compile error, Case without Select Case" on this line: "Case "DINERS ONLY NEW MERCHANTS""

I've looked over it thoroughly so it must be something very small.

Please Login or Register  to view this content.

**snb** · 10-08-2010, 08:17 AM

It's evident that the first row shouldn't be included.

Please Login or Register  to view this content.

**swathyp** · 10-08-2010, 08:32 AM

Hi

I have an exel likes this

NAME LOC DETAILS
A XX 1000,SWATHY
B XX 2000,NANDHU
C XX 1000,BEENA
D YY 1000,ANU
E XX 2000,MAYU
F YY 3000,RAMYA
F XX 4000,JINU

I need to sort DETAILS row and get all row which contains the same data many times.like:

NAME LOC DETAILS
A XX 1000,SWATHY
C XX 1000,BEENA
D YY 1000,ANU
B XX 2000,NANDHU
E XX 2000,MAYU

can any one help me please....

swathi

**Poulan** · 10-08-2010, 08:34 AM

You need to start a new message thread because this is unrelated to the current discussion.

**Marcol** · 10-08-2010, 08:42 AM

Try this to see if it is what you are after.

Please Login or Register  to view this content.

Is this correct? If not post a sample workbook with more typical data, your last sample doesn't cover your criteria

**Poulan** · 10-08-2010, 08:51 AM

You are an awesome person. I hope that my words make you sit back and feel great for at least the length of time that you spent looking through this for me.

**Marcol** · 10-08-2010, 09:00 AM

That is perhaps not the best code for your purpose, but I feel that it will help you see how to step through your process, and then, when you understand how it works, you can improve it yourself.

Happy to have helped

Please mark your thread [Solved] if you are happy that your question has been answered.

Delete rows based on multiple criteria (Date & Text)

LinkBack

Thread Tools

Rate This Thread

Display

Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

sorting the row and get all row which contains the same data many times

Re: sorting the row and get all row which contains the same data many times

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Re: Delete rows based on multiple criteria (Date & Text)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions