Delete duplicates multiple columns and if blank additional column

davahill

New Member
Joined
Jan 16, 2014
Messages
21
Hello Experts,
I've been attempting to create a macro to delete duplicate rows and leave only one (excluding header row) where columns A, C, E, F and H are duplicates. Sometimes column E and F may be empty. I'd like to delete the duplicates only if column J is blank. I've tried the code below but it does not work.

VBA Code:
Sub DeleteDuplicatesIfBlank()
   
    Const dColsList As String = "A,C,E,F,H" ' Duplicate Columns List (2 at least)
    Const bCol As String = "J" ' Blank Column
    Const Delimiter As String = "||" ' Dictionary Delimiter
   
    ' Create a reference to the range ('rg').
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("Worklist Data")
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
   
    ' Write the Duplicate columns to the Duplicate Columns Array ('dCols').
    Dim dCols() As String: dCols = Split(dColsList, ",")
    Dim dUpper As Long: dUpper = UBound(dCols)
   
    ' Define the Dictionary ('dict').
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' ignore case i.e. A=a
   
    ' Declare additional variables.
    Dim drg As Range ' Delete Range
    Dim rrg As Range ' Current Row Range
    Dim n As Long ' Duplicate Column Counter
    Dim cString As String ' Current String (created from the Duplicate Columns)
   
    ' Loop through each row ('rrg') of the range.
    For Each rrg In rg.Rows
        ' Only consider the row if its Blank Column is blank.
        If Len(CStr(rrg.Columns(bCol).Value)) = 0 Then
            ' Concatenate the strings from the Duplicate Columns to 'cString'.
            cString = CStr(rrg.Columns(dCols(0)).Value) ' first
            For n = 1 To dUpper ' remainder
                cString = cString & Delimiter & CStr(rrg.Columns(dCols(n)))
            Next n
            If dict.Exists(cString) Then ' duplicate found
                ' Combine the Current Row Range into the Delete Range.
                If drg Is Nothing Then
                    Set drg = rrg
                Else
                    Set drg = Union(drg, rrg)
                End If
            Else ' not a duplicate, so add it to the Dictionary
                dict(cString) = Empty
            End If
        'Else ' The Blank Column is not blank: do nothing.
        End If
    Next rrg
   
    If drg Is Nothing Then Exit Sub ' no duplicates found
   
    ' Delete the rows containing duplicates. Possible data to the right
    ' of the range will not be affected.
    drg.Delete ' for entire rows use drg.EntireRow.Delete
   
End Sub

Any assistance would be greatly appreciated.
 
Last edited by a moderator:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,176,670
Messages
5,904,378
Members
435,088
Latest member
Kingsof82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top