Macro to Filter and delete Duplicate rows

**Roop** · 12-28-2010, 05:12 PM

Hi guys,
I have a worksheet with 10 columns of data in it. All columns have headers. I want to write a macro to filter data in Column A on criteria X first and then Column B for criteria Y and then go look for duplicates in column E and F and delete that entire row. I mean for example if E11 and F11 are same as E20 and F20, then delete Row20.

Any help will be highly appreciated.

Thank you.

**MarvinP** · 12-28-2010, 05:46 PM

Hi Roop,

Is the problem to get a better set of data or to learn to write macros?

You can use Advanced Filter to get to one state and remove duplicates for the second.

What did you really want?

**Roop** · 12-28-2010, 07:48 PM

Hi marvin ,
i am learning to write macros. The real problem i am having is to specify the first row and last row for my range becuase they change when i change my filter criteria. So, any help you can provide me for finding out the first row below header and last row of the visible data after applying filter will mcuh appreciated .

Thank you.

**MarvinP** · 12-28-2010, 08:26 PM

Hi Roop,
Finding the last non-blank cell in a row is a standard for writing most VBA code.

Look on the net for xlUp on the net. I've had to learn this myself and there are a few ways to get this number.

For the last row I've standardized on the code:

Dim LastRow as Double
LastRow = Cells(Rows.count, "A").End(xlUp).Row

There are many other ways to get this important number also.
Read: http://www.tushar-mehta.com/excel/vb...and%20code.htm for many others.