Removing duplicate value from excel file using vbs

0
votes

I am trying to remove duplicate value from column U in excel file, while all other columns are empty. Since i am using UsedRange function i am encountering a runtime error. I am using the following code:

 Set objXL = CreateObject("Excel.Application")
 Set objWB = objXL.Workbooks.Open(basedir & ss)
 Set objWS = objWB.Worksheets("Report 2")
 objWB.Saved = True
 objWB.ActiveSheet.UsedRange.RemoveDuplicates Array(20), 1
 objWS.Rows("1:2").Delete
 objWS.Columns("A:T").Delete

 call  objWB.SaveAs(temp_file_bu2,24)
 objWB.Saved=True

I tried using Range function also for choosing column and it is not working. If all column under range of UsedRange are filled with duplicate value only the function is working.

2
excelvbavbscript
U column is the 21th column. But your code is about column 20. Why? – Herry Markowitz
Ya that was very stupid mistake. thanks for pointing out. – user3201928

2 Answers

0
votes

Replace this;

 objWB.ActiveSheet.UsedRange.RemoveDuplicates Array(20), 1

with this

objWB.ActiveSheet.UsedRange.RemoveDuplicates Columns:=21, Header:=xlYes
0
votes

If all other columns are empty replace this;

 objWB.ActiveSheet.UsedRange.RemoveDuplicates Array(20), 1

with this

objWB.ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes