WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Remove Duplicate Records in Excel

 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