How can I remove duplicates from a dynamic range?

[roxnoxsox]

I've used 'Record Macro' to record using the 'Remove Duplicates' button, which will delete rows in my data depending if the contents of column E, H, I, J, M, N and O are all the same.

The resultant macro is this:

    ActiveSheet.Range("$A$1:$Q$3277").RemoveDuplicates Columns:=Array(5, 8, 9, 10, 13 _
        , 14, 15), Header:=xlYes

Is it possible to adjust this to a dynamic range as my data will change every day (column order would still be the same). But I may have 5000 or more rows rather than 3277 as specified above. Would I just change this to: ("$A:$Q") or would that not work?

Many thanks for any suggestions! I'm new to using the Remove Duplicates function

[p45cal]

If the table is bounded by blank column R and blank row below (and no completely blank rows or columns within the table) you might get away with:
ActiveSheet.Range("A1").currentregion.Remov~~~
or variant:
Intersect(ActiveSheet.Range("A:Q"),ActiveSheet.Range("A1").currentregion).R emov~~~
If the table is a proper Excel Table (listobject in vba) then:
Range("Table1").Remov~~~

Come back if none of these work.

Your .range("$A:$Q") should work but is in danger of removing stuff below the table too.

[roxnoxsox]

Hi p45cal - many thanks for your above suggestions. With regards to my question of whether ("$A:$Q") would work - I have no data below the table. This is everything on the worksheet- so would that be ok?

[p45cal]

Yes, as long as the headers are on row 1 of the sheet (you have, quite rightly: Header:=xlYes in the code).

[roxnoxsox]

Great, I'll just use this then Many thanks for explaining!