Database Search

TechHome

• Auto Sum Two Columns In Excel
• Basic iteration of Slicers in Excel
• Check if Workbook Contains a Macro
• Combine XLS Workbooks using ADO
• Determine if XLS File is Password Protected
• Read Excel Cells Into WIL Dialog
• Remove Duplicate Records in Excel
• RemoveDuplicates

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

RemoveDuplicates

 Keywords:  RemoveDuplicates Remove Duplicates Excel

;Remove Duplicates from User Specifed Range
title = "Excel Files"
path = DirScript()
mask = "XLS Files (*.xlsx)|*.xlsx"
xls = AskFilename(title, path, mask, "", 1)

; Create Excel Object
objXL = ObjectCreate("Excel.Application")
objXL.Visible = @TRUE
objXL.DisplayAlerts = @FALSE

; Open XLS file
objWorkbooks = objXL.Workbooks
objWorkbooks.Open(XLS)

; An array of indexes of the columns that contain the duplicate information.
arrCols = ArrDimension(7)
For x = 0 To ArrInfo(arrCols, 1 )-1
   arrCols[x] = x+1
Next

range = AskLine( 'Specify Range', 'Specify Range where format = $A$1:$G$99', '$A$1:$G$99' )

objXL.ActiveSheet.Range(range).RemoveDuplicates(:: COLUMNS = arrCols, Header = 0 )

Pause('Finished', 'Removing duplicates')

objXL.Save()
objWorkbooks.Close()
objXL.Quit

objRange = 0
objWorkbooks = 0
objXL = 0

;Remove Duplicates from Entire Used Range
title = "Excel Files"
path = DirScript()
mask = "XLS Files (*.xls)|*.xls;*.xlsx"
xls = AskFilename(title, path, mask, "", 1)

; Create Excel Object
objXL = ObjectCreate("Excel.Application")
objXL.Visible = @TRUE
objXL.DisplayAlerts = @FALSE

; Open XLS file
objWorkbooks = objXL.Workbooks
objWorkbooks.Open(XLS)

objRange = objXL.ActiveSheet.usedRange
columncount = objRange.Columns.Count

; An array of indexes of the columns that contain the duplicate information.
arrCols = ArrDimension(columncount)
For x = 0 To ArrInfo(arrCols, 1 )-1
   arrCols[x] = x+1
Next

objRange.RemoveDuplicates(:: COLUMNS = arrCols, Header = 0 )

Pause('Finished', 'Removing duplicates')

objXL.Save()
objWorkbooks.Close()
objXL.Quit

objRange = 0
objWorkbooks = 0
objXL = 0

Article ID:   W18121

Filename:   RemoveDuplicates.txt

File Created: 2011:09:28:07:35:46

Last Updated: 2011:09:28:07:35:46