I created this code to remove duplicates through the advanced filter,
but it`s very specific to a particular size and set of data, for example
columns F:F. How could it be adjusted so that it would work on all
types of data regardless of number of columns or rows?

Sub removeduplicates()

'highlight all data from which to remove duplicates
Sheets("Sheet1").Select
Cells.FindNext(After:=ActiveCell).Activate
Columns("F:F").Select
Selection.Clear
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:E13").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

'copy all visible lines in filter-in-place using a go to
and paste-special value to a new sheet
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "file no duplicates"
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

'in the new worksheet add a column, create a heading called "inventory"
and fill all the rows in the dataset with "yes" for this column
Range("E1").Select
Selection.End(xlDown).Select
Range("F6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "xxx" 'I use a system of xxx to find and
fill to the last row of the dataset, but would appreciate knowing a
better way to do this
Range("F5").Select
Selection.End(xlUp).Select
ActiveCell.FormulaR1C1 = "inventory"
Range("F2").Select
ActiveCell.FormulaR1C1 = "yes"
Range("F2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

'copy paste special values
Range("F6").Select
Application.CutCopyMode = False
Selection.ClearContents
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

'delete the original file which has the duplicates
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
End Sub

Thank you.

Marina Madeleine


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!