CODEWITHSUNDEEP
xmlvbaexcel
venky_in
venky_in

Reputation: 19

Remove XML duplicate items in Excel using VBA

I have 10 Excel sheets each I've loaded with different XML feed connections.
So, In order to get followed those 10 list updates regularly I used XML append data!

But the problem here is, that feed is giving duplicate items each time I XML table refresh.

Now, I want a VBA code remove all those duplicate items in all sheets at once.

Here is the code I am using:

Sub Macro_Table()
    Dim ws As Worksheet
    Dim xmltable As LisObject

    For Each ws In ThisWorkbook.worksheets
        Set xmltable = ws.ListObjects(1)
        xmltable.Range().RemoveDuplicate Columns:=Array(4), Headers:= Yes
    Next ws
End Sub

But when running this - the following error is coming:

Sub script out of range

Upvotes: 2

Views: 1152

Answers (1)

Comintern
Comintern

Reputation: 22185

I'm guessing the error is that you hit a Worksheet that doesn't contain a ListObjects(1). The reason it has to be on that line is because the line underneath it won't compile at all:

xmltable.Range().RemoveDuplicate Columns:=Array(4), Headers:= Yes

...should look more like this:

xmltable.Range.RemoveDuplicates Columns:=Array(4), Header:=xlYes

You need to remove the parentheses from the .Range() call unless you are referencing a sub-range. "Headers:=" is not an argument name, it should be "Header:=", and you need to use the enumeration xlYes instead of the undefined value "Yes". Finally, RemoveDuplicate should be RemoveDuplicates.

If you don't have Option Explicit on, Yes will be a new Variant, get assigned a default value of 0, and be passed to the function as basically xlGuess.

You should also correct your variable declaration, Dim xmltable As ListObject instead of LisObject.

Put it back together with a check to see if the ListObject exists, and you get something like this:

Sub Macro_Table()
    Dim ws As Worksheet
    Dim xmltable As ListObject

    For Each ws In ThisWorkbook.Worksheets
        If ws.ListObjects.Count <> 0 Then
            Set xmltable = ws.ListObjects(1)
            xmltable.Range.RemoveDuplicates Columns:=Array(4), Header:=xlYes
        End If
    Next ws
End Sub

Upvotes: 3

Related Questions