VBA: Remove Range for Dynamic Named Range

[Seba Robles]

Hello,

I am trying to use VBA to remove duplicates in a named range but I can't get it to work.

My dataset has 7 columns, A through G. Column A is the one with the unique key.

The line I'm trying to use to remove duplicates is

SourceRng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes

I think it's not really finding duplicates because it's considering all other columns (A through G)? However, I don't know how to get that part down.

Here is my full code --- line 73 has where I'm trying to remove duplicates:

Sub UpdateData()

Dim Confirm As Integer
Confirm = MsgBox("Do you wish to update the file?", vbYesNo)

If Confirm = vbYes Then
     
    'Unhide Raw Data worksheet
    Sheets("Raw Data").Visible = True
 
    'Declare and set variables
    Dim Summary_ws As Worksheet: Set Summary_ws = ThisWorkbook.Worksheets("Summary")
    Dim Source_ws As Worksheet: Set Source_ws = ThisWorkbook.Worksheets("Source")
    Dim RawData_ws As Worksheet: Set RawData_ws = ThisWorkbook.Worksheets("Raw Data")
    Dim ExportRng As Range: Set ExportRng = Summary_ws.Range("ExportData")
    Dim SourceRng As Range: Set SourceRng = Source_ws.Range("SourceData")
 
    Application.StatusBar = "Updating data, please wait..."
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
 
    RawData_ws.Activate
 
    'Remove all active filters
    On Error Resume Next
    RawData_ws.ShowAllData
 
        'Delete previous raw data
        Range("A3").EntireRow.Select
        Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp
        Range("A2:D2").ClearContents
 
    'Search and select CO data Excel file
    Application.DefaultFilePath = "C:"
    f = Application.GetOpenFilename("ExcelWorkbook(*.*),*.*,", , "Select CO file to import")
    If f = "False" Then Exit Sub
 
    'Load Data
    Set W = Workbooks.Open(f)
    Set W1 = W.ActiveSheet
 
        'Copy CO list
        W1.Activate
        Range("A5").End(xlDown).Offset(-1).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlUp)).Offset(1).Copy
 
        'Paste CO list into Master Roster file
        RawData_ws.Activate
        Range("A2").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
     
        W.Close False

        'Extend formulas
        Range("A1").End(xlDown).Offset(0, 9).Select
        Range(Selection, Selection.End(xlToLeft)).Offset(0, 1).Select
        Range(Selection, Selection.End(xlUp)).FillDown
     
    'Crosscheck imported CO list against Source worksheet
    Range("E2:H2").Select
    Range(Selection, Selection.End(xlDown)).Copy
    Source_ws.Activate
    Range("B1").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteValues
 
        'Refresh CONCATENATE column
        Range("A3").Select
        Range(Selection, Selection.End(xlDown)).ClearContents
        Range("B1").End(xlDown).Offset(0, -1).Select
        Range(Selection, Selection.End(xlUp)).FillDown
 
    'Remove duplicates
    SourceRng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes
 
        'Sort table A to Z
        With Source_ws
            SourceRng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
        End With
 
    'Refresh all pivot tables
    ThisWorkbook.RefreshAll
    ThisWorkbook.RefreshAll
     
    'Hide Raw Data worksheet
    RawData_ws.Visible = False
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.StatusBar = "Data succesfully updated."
 
    Summary_ws.Activate
    Range("A1").Select
 
    MsgBox ("File has been succesfully updated.")
 
Else 'Do nothing

End If

End Sub

Let me know if you need additional information.

Thanks in advance!

 

[Fluff]

Try it like

SourceRng.RemoveDuplicates Columns:=1, Header:=xlYes

 

[Seba Robles]

Thank you! I did that without using the named range and it worked. For some reason it wasn't working when I used the named range.

 

[Fluff]

You're welcome & thanks for the feedback.