How can I modify this macro (arrays) for exclude the headers and extend the range of columns analyzed?

[Lehoi]

Hi all

I found this code in the web to compare two worksheets and delete duplicates.
I made some minor changes and it works decently, but I am stuck with this 2 things:
1- Exclude the headers
2- Analyze just not the column H, I need analyze the range from C to K, if all cells are identical in both sheets, delete duplicates (only the range C to K).

Any idea please? ray:
Thanks

Sub CleanDupes()

Dim targetArray, searchArray
Dim targetRange As Range
Dim x As Long


'Update these 4 lines if your target and search ranges change
Dim TargetSheetName As String: TargetSheetName = "Daily Data"
Dim TargetSheetColumn As String: TargetSheetColumn = "H"
Dim SearchSheetName As String: SearchSheetName = "Matches Added"
Dim SearchSheetColumn As String: SearchSheetColumn = "H"


Application.ScreenUpdating = False
'Load target array
With Sheets(TargetSheetName)
    Set targetRange = .Range(.Range(TargetSheetColumn & "1"), .Range(TargetSheetColumn & Rows.Count).End(xlUp))
    targetArray = targetRange
End With
'Load Search Array
With Sheets(SearchSheetName)
    searchArray = .Range(.Range(SearchSheetColumn & "1"), .Range(SearchSheetColumn & Rows.Count).End(xlUp))
End With


Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = 0
If IsArray(searchArray) Then
    For x = 1 To UBound(searchArray)
        If Not dict.exists(searchArray(x, 1)) Then
            dict.Add searchArray(x, 1), 1
        End If
    Next
Else
    If Not dict.exists(searchArray) Then
        dict.Add searchArray, 1
    End If
End If


'Delete rows with values found in dictionary
If IsArray(targetArray) Then
'Step backwards to avoid deleting the wrong rows.
    For x = UBound(targetArray) To 1 Step -1
        If dict.exists(targetArray(x, 1)) Then
            'targetRange.Cells(x).EntireRow.Delete
            targetRange.Cells(x).ClearContents
        End If
    Next
Else
    If dict.exists(targetArray) Then
        targetRange.EntireRow.ClearContents
    End If
End If
Intersect([C:K], Columns("H").SpecialCells(xlBlanks).EntireRow).Delete xlUp
Application.ScreenUpdating = True
End Sub

 

[Leith Ross]

Hello Lehoi,

Is it possible that the contents in a row in columns C through K on one sheet could be the same but in different order on the other sheet?

 

[Lehoi]

Hi Leith Ross, thanks for replay

The data in sheet "Matches Added" is in unique order because first I extract the data from the web and copy in sheet "Daily Data", then I copy some rows (from C to K) to sheet "Matches Added" with another macro.
The purpose of this macro is when I extract data again and paste in "Daily Data", I want to remove the data already exist in sheet "Matches Added" and only see new data.
sorry my poor english

 

[Leith Ross]

Hello Lehoi,

Yo creo que se puede escribir mejor en Inglés que puedo escribir en Español.

If you could post a copy of your workbook to a public file sharing site, it would make it easier for me to provide you with a working solution.

 

[Lehoi]

Great spanish Leith Ross

This is the link:
https://mega.nz/#!6sQHQYBa!Juh7w7DfYGw9M9noTVVTLNrQasz302sWXVg--HuthWY

hope you can find a solution

 

[Lehoi]

Hi
I asked the same question in spanish forum and Igtelo gave me a solution, good alternative to arrays using Concatenate function. Thanks!
Como modificar esta macro (usa arrays) para que me excluya los headers y me analize un rango de columnas y no solo una?

regards

 

[Leith Ross]

Hello Lehoi,

Lo siento, yo era muy tarde en la respuesta a su pregunta. Me complace que recibió una respuesta.

(Gaélico Escocés - A tu salud!)
Slàinte mhath!

 

[Lehoi]

Hello Lehoi,

Lo siento, yo era muy tarde en la respuesta a su pregunta. Me complace que recibió una respuesta.

(Gaélico Escocés - A tu salud!)
Slàinte mhath!

No problem Leith, I really appreciate your interest!
No hay problema Leith, aprecio mucho su interés!
Saludos