When I use the advanced filter it gets rid of the duplcate rows, but, some of the rows use more columns than others. After I have pasted the data from the advanced filter it only pastes the first two columns. Some of the rows use up to 5 columns. Why is it doing this and how can i get round it?
Copy the headings from the original columns ot the new location then do the advanced filter. If you have copied the headings to say H1:L1, then in the copy to range of the advanced filter dialogue box type H1:L1. this should copy all of the columns across.
This will do the trick, if the column you want is the farthest left and is sorted.
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Cells(rowx, 1).Value) = UCase(Cells(rowx + 1, 1).Value) Then
Cells(rowx + 1, 1).EntireRow.Delete
rowx = rowx + 1
Takes a little while. But I did one with 16,000 rows last Friday - and it took about 4 minutes to run. Far better than by hand, though.