macro to select table and remove duplicates problem.

[0lzi]

hi guys,

i recorded a macro that selects a sheet, highlights a table that is created from a MS query, then removes duplicates, then displays a msgbox to inform the user.

Sub Macro6()
'
' Macro6 Macro
'

'
    Sheets("Raw Data").Select
    ActiveSheet.Range("Table_Query_from_SFS[#All]").RemoveDuplicates Columns:= _
        Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Header:=xlYes
        
        If xlYes Then MsgBox ("Duplicates Removed")
        
End Sub

the problem i have is i am calling this macro from another procedure, which works fine, but and the msgbox displays "Duplicates Removed" but it doesnt actually remove duplicates because i can manually goto the sheet after the procedure has finished and remove duplicates still, it the marco was working there would be no duplicates left and would get a msg saying that.

any help would be appreciated

 

[Andrew Poulsom]

What's xlYes?

 

[0lzi]

the xlYes is used above in the Header:xlYes which when debugging is coming out true, so i used it in that 'if'

probably the wrong way to go about it but, the msgbox is essentialy just to check if the remove duplicates command actually worked, but it doesnt.

 

[Andrew Poulsom]

xlYes is a VBA constant with a value of 1, so it will always be True.

 

[0lzi]

i get that, thats why i used it for my 'IF' statement.

what im getting at was the duplicates should have been removed because Header:=xlYes

    ActiveSheet.Range("Table_Query_from_SFS[#All]").RemoveDuplicates Columns:= _
        Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Header:=xlYes

the code shouldnt move on to the if statement untill this statement is done, but i cant get it to actually remove duplicates which is the problem.

 

[Andrew Poulsom]

You won't see your message box until the RemoveDuplicates is done. But that doesn't mean that the RemoveDuplicates was successful. What happens with?

    Sheets("Raw Data").Range("Table_Query_from_SFS[#All]").RemoveDuplicates Columns:= _
        Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Header:=xlYes

Generally there is no need to select an object before using its methods/properties. Do you have any error traps in your code?

 

[0lzi]

that code was done my recording a macro where i selected all the data in the table and removed duplicates, but when its called, the select sheet works but it doesnt appear to remove duplicates beause i can do it manually after the procedure has done and the msgbox shows up saying the duplicates have been removed.

and for this macro i havent put any error traps in, as i dont get any errors when it runs, it just doesnt do what its supposed to.

 

[0lzi]

plus im just coming up to the chapter in my vba book about error-handling, so hopefully il have some more info about how its done in vba

 

[Andrew Poulsom]

Sorry, I can't think of a reason for it not working.