PDA

View Full Version : [SOLVED] How can I remove duplicates from a dynamic range?



roxnoxsox
02-15-2016, 04:48 AM
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
02-15-2016, 05:03 AM
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).Remov~~~
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
02-15-2016, 06:11 AM
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
02-15-2016, 06:32 AM
Yes, as long as the headers are on row 1 of the sheet (you have, quite rightly: Header:=xlYes in the code).

roxnoxsox
02-15-2016, 06:46 AM
Great, I'll just use this then :) Many thanks for explaining!