Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing duplicates
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! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Duplicates Help | Excel Discussion (Misc queries) | |||
Removing Duplicates | Excel Worksheet Functions | |||
removing duplicates | Excel Discussion (Misc queries) | |||
Removing Duplicates | Excel Discussion (Misc queries) | |||
Removing Duplicates | Excel Worksheet Functions |