Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: Remove Delete Duplicate Records Rows Filter AdvancedFilter xlFilterCopy CopyToRange Unique Excel
cXLS = 'D:\temp\Data\class_list.xlsx' oXL = CreateObject("Excel.Application") If oXL == 0 Then Exit oXL.Visible = 1 oXL.ScreenUpdating = 1 oXL.UserControl = 1 oXL.DisplayAlerts = 0 nOld = oXL.SheetsInNewWorkbook oXL.SheetsInNewWorkbook = 1 oXL.WorkBooks.Open(cXLS) oWKB = oXL.ActiveWorkBook oSheet = oWKB.ActiveSheet ; Obtain last row and column TOTAL_ROWS = oSheet.UsedRange.Rows(oSheet.UsedRange.Rows.Count).Row TOTAL_COLS = oSheet.UsedRange.Columns(oSheet.UsedRange.Columns.Count).Column Pause('Row and Columns count', 'TOTAL_ROWS = ': TOTAL_ROWS: @LF : 'TOTAL_COLS = ': TOTAL_COLS) ; Tranlate column numbers into the letter equivilent alphalist = StrUpper('a b c d e f g h i j k l m n o p q r s t u v w x y z aa ab ac ad ae af ag ah ai aj ak al am an ao ap aq ar as at au av aw ax ay az') ;Modify to fit your needs ; Get column letter of the last column COL_LETTER = ItemExtract( TOTAL_COLS, alphalist, ' ' ) If COL_LETTER == 0 Pause('Notice','Unable to locate column letter in alphalist') Exit EndIf ; Get column letter of the column just after the last column NEXT_COL_LETTER = ItemExtract( TOTAL_COLS+1, alphalist, ' ' ) If NEXT_COL_LETTER == 0 Pause('Notice',) Exit EndIf ; Get column letter of the destinations ranges last column END_COL_LETTER = ItemExtract( TOTAL_COLS+TOTAL_COLS, alphalist, ' ' ) If NEXT_COL_LETTER == 0 Pause('Notice',) Exit EndIf ; Define source range sourcerange = "A1:":COL_LETTER:TOTAL_ROWS oRangeSource = oSheet.Range(sourcerange) ; Define destination range destinationrange = NEXT_COL_LETTER:"1:":END_COL_LETTER:TOTAL_ROWS oRangeDest = oSheet.Range(destinationrange) ; Remove Duplicate Rows Copying filtered data to destination range xlFilterCopy = 2 oRangeSource.AdvancedFilter(:: Action=xlFilterCopy, CopyToRange=oRangeDest, Unique=@TRUE ) ; Save Workbook oXL.ActiveWorkBook.Save() ;Close workbook oXL.ActiveWorkBook.Close() oXL.Quit() oXL=0 Exit
Article ID: W18120
Filename: Remove Duplicate Records in Excel.txt
File Created: 2010:10:19:09:33:56
Last Updated: 2010:10:19:09:33:56