How do I fix this? Delete Duplicate Columns

[ParanoidAndroid]

Hi Excel Experts

I really appreciate your help with this

I have adapted this excel code i found online. This code looks for duplicates in column V and then deletes that row with the duplicate.

There is however a slight quirk -

The sheet has a range (A:U) which is linked to a database in Sharepoint. So when deleting the row based on duplicate cell in column V I need to

1) Delete the duplicate cell in column V and shift up that cell
ie: Range("V" & CStr(LTestLoop)).Select
Range("V" & CStr(LTestLoop)).Delete Shift:=xlUp

2) select a cell from the row I want to delete in the linked range and delete that row
ie. something like Selection.ListObject.ListRows.Delete

This is because it doesnt seem possible to use EntireRow.Delete when you have a linked range(to Sharepoint).

He is the code i've adapted...it seems to work except i'm not sure how to adapt 2) see "delete duplicates

Sub TestForDuplicates()
    Dim LLoop As Integer
    Dim LTestLoop As Integer
    Dim Lrows As Integer
    Dim LRange As String
    Dim LCnt As Integer
    'Column values
    Dim LColV_1 As String
    
    
    Lrows = Range("V2").End(xlDown).Row
    LLoop = 2
    LCnt = 0
    
    'Check until last used row in column V
    While LLoop <= Lrows
        LColV_1 = "V" & CStr(LLoop)
       
        If Len(Range(LColV_1).Value) > 0 Then
            'Test each value for uniqueness
            LTestLoop = LLoop + 1
            While LTestLoop <= Lrows
                If LLoop <> LTestLoop Then
                    LColV_2 = "V" & CStr(LTestLoop)
                    
                    'Value has been duplicated in another cell of the same coluumn which is column V)
                    If (Range(LColV_1).Value = Range(LColV_2).Value) _
                      Then
                        'Delete the duplicate
                        Rows(CStr(LTestLoop) & ":" & CStr(LTestLoop)).Select
                        Selection.ListObject.ListRows(LTestLoop).Delete             'deletes a row within the range linked to sharepoint database
                        Rows(CStr(LTestLoop) & ":" & CStr(LTestLoop)).Select
                        Range("V" & CStr(LTestLoop)).Select                 'deletes the cells tested in column that is outside the linked sharepoint database otherwise it gets stuck in a loop
                        Range("V" & CStr(LTestLoop)).Delete Shift:=xlUp
                        'Decrement counter since row was deleted
                        
                        LTestLoop = LTestLoop - 1
                        LCnt = LCnt + 1
                    End If
                End If
                LTestLoop = LTestLoop + 1
            Wend
        End If
        LLoop = LLoop + 1
    Wend
  
 
End Sub

 

[Krishnakumar]

Hi,

Try

Sub kTest()
    Dim ka, k(), i As Long, r As Range, n As Long
    
    With ActiveSheet
        Set r = Range("v2:v" & .Range("v" & .Rows.Count).End(xlUp).Row)
        ka = r
    End With
    ReDim k(1 To UBound(ka, 1), 1 To 1)
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(ka, 1)
            If Len(ka(i, 1)) Then
                If Not .exists(ka(i, 1)) Then
                    n = n + 1
                    k(n, 1) = ka(i, 1)
                    .Add ka(i, 1), Nothing
                End If
            End If
        Next
    End With
    With r
        .ClearContents
        .Value2 = k
    End With
End Sub

HTH

 

[ParanoidAndroid]

Thanks Krish

I will try tomorrow when i have access to the file.

I'm just trying to understand the code...So which line actually deletes the duplicate row??? I'm not seeing or understanding it?

 

[ParanoidAndroid]

Hi Excel

It would be great if someone could kindly explain the code Krish has supplied? I don't understand it

Thanks

 

[Krishnakumar]

Hi,

Have a look at this

 

[ParanoidAndroid]

Thanks Krish.

I sort of understand - so we are using a dictionary which uses a key index and that numerical.

I tested your code..and it works a treat for elimating the duplicated in the column V

But it does not elimatinate the duplicates in the same row from A to U as the duplicate cell in column V.

Since r is a dictionary key (if i understand this correctly) i dont see how we can delete the relevate row??

Can we adapt this code?

I appreciate you help and patience..I sort of understand VBA but im far from an expert...

 

[Krishnakumar]

Hi,

Try

Sub kTest()
    Dim ka, k(), i As Long, r As Range, n As Long, c As Long
    
    With ActiveSheet
        Set r = Range("a2:u" & .Range("v" & .Rows.Count).End(xlUp).Row)
        ka = r
    End With
    ReDim k(1 To UBound(ka, 1), 1 To 1)
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(ka, 1)
            If Len(ka(i, 22)) Then
                If Not .exists(ka(i, 22)) Then
                    n = n + 1
                    For c = 1 To UBound(ka, 2)
                        k(n, c) = ka(i, c)
                    Next
                    .Add ka(i, 22), Nothing
                End If
            End If
        Next
    End With
    With r
        .ClearContents
        .Value2 = k
    End With
End Sub

HTH

 

[ParanoidAndroid]

Hi Krish

I really appreciate your help

i just tried it...No luck

it says "run time error '9'

sub script out of range..

I created a fresh/new sheet and typed in unique dummy data from columns a:u for 10 rows and 1 or 2 in each cells in columns V (for duplicates)...

No luck

but in the actual sheet i use - the region to be deleted in A:U is linked to Sharepoint database. If I run a the macro recorder and delete a row it applies this code...Selection.ListObject.ListRows(1).Delete so it might be work keeping this in mind.

 

[Krishnakumar]

Hi,

Sorry

replace

Set r = Range("a2:u" & .Range("v" & .Rows.Count).End(xlUp).Row)

with

Set r = Range("a2:v" & .Range("v" & .Rows.Count).End(xlUp).Row)

 

[ParanoidAndroid]

Hi Krish

I tested it again, changing the U to V as suggested. It still doesnt work. I get exactly the same message.

I tested it on a fresh sheet/file with random data from A to V for first 10 rows.

I put it into a module -

when i debug it says this line is the problem

k(n, c) = ka(i, c)

Could it be because im using excel 2003?