If Function Based On Criteria to Delete

**numbersdntlie** · 02-04-2011, 05:08 PM

Hi All,

I have a very large excel file with 2900 rows and 25 columns. I would like an if function or method that will compare what in column A with column B and delete the entire row it if the criteria doesnt match. For Example :

Column A Column B

FHIT HOT
FHIT COLD
FHIT COLD
RFKP COLD
RFKP HOT
DUGH HOT
DEPS COLD
DEPS HOT

What i would like to happen is have the entire 25 columns in each row deleted: if there are 2 or more columns A's with the same info (FHIT-for example) I would like the row or rows with "cold" to be deleted. If there isn't 2 or more matching column A i would like the row to not be deleted. Any help will be greatly appreciated thanks.

**nimrod** · 02-04-2011, 05:36 PM

Please Login or Register  to view this content.

**numbersdntlie** · 02-04-2011, 07:14 PM

Hi, thanks for the macro. That seems to work for the first 3 rows. How do i get that process ran for all 2900 rows?

Thanks for the help

**watersev** · 02-04-2011, 10:06 PM

see attachment, run macro "test" (tested on 2900 rows and 29 columns, takes 6.28 sec). Check result on sheet "2". The code processes rows from 1 to 2900 only

**numbersdntlie** · 02-05-2011, 04:02 AM

Thanks for the assistance but the macro could not be ran.

**watersev** · 02-05-2011, 05:17 AM

Make sure the following reference is on:
1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. Run macro "test"

**nimrod** · 02-05-2011, 07:30 PM

Hello numbersdntlie:
The macro I provided should work on any number of rows in fact I've tested it on 500 rows with no problem ... could you provide a sample sheet so I can see what's going on ?

**numbersdntlie** · 02-06-2011, 05:29 AM

Hi nimrod-

Thanks for your help on this. See attached file. As you can see in the "Ex Data" column there are some rows with matching info ( i sorted data to make more visable) but has different statuses in column B. What I wanted to happen which your macro did but not on the whole sheet is to have the items in Column A that appear more than once (ex.MAR) but has conflicting statuses is to have the rows with the status of Pending to be deleted. If there is no conflicting rows for a column A item I would like it to stay. I would like the whole entire row up to column Z to be deleted for each time the macro deletes. thanks for your help.