Case Sensitive Duplicate Removal in Array

bayles

Board Regular
Joined
Oct 31, 2013
Messages
54
Hi,

I have a data range in columns A:G. I want to remove data in each row in columns A:G based on duplicate values in column G.

The following code works well to remove duplicates in column G alone however I want to remove the data in all columns of the row with the duplicate value.

Any help appreciated.

Ryan


Dim a, e, x


For i = 1 To 5
Cells(1, i * 7).Select
With Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column))
a = .Value
.ClearContents
With CreateObject("Scripting.Dictionary")
For Each e In a
.Item(e) = Empty
Next
x = .keys
End With
.Resize(UBound(x) + 1).Value = Application.Transpose(x)
End With
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi,

It looks from the initial For loop that you want to repeat this for other groups of seven columns. If that is so, do you want the entire row to be deleted or just the seven columns in that iteration of the loop?

Aso, I think your second loop will run for all rows not just the populated ones in the column.
 
Upvote 0
Hi,

I just want to delete the data in thos 7 columns because as you stated it will run for other groups of columns.

That is fine if it runs for all rows as they will be empty anyway.

Thanks for your assistance.
 
Upvote 0
Hi,

Try this:
Code:
Sub delRows()

    Dim ws As Worksheet
    Dim arrIn As Variant
    Dim arrOut As Variant
    Dim i As Long, j As Long, k As Long
    Dim dic As Object
    
    Set ws = ThisWorkbook.ActiveSheet
    Set dic = CreateObject("Scripting.Dictionary")
    
    With ws
    
        For i = 7 To 35 Step 7
            arrIn = .Range(.Cells(1, i - 6), .Cells(.Cells(.Rows.Count, i).End(xlUp).Row, i)).Value
            dic.RemoveAll
            For j = 1 To UBound(arrIn)
                dic(arrIn(j, 7)) = j
            Next
        
            ReDim arrOut(1 To dic.Count, 1 To 7)
            For j = 1 To dic.Count
                For k = 1 To 7
                    arrOut(j, k) = arrIn(dic.Items()(j - 1), k)
                Next
            Next
            
            .Columns(i).Offset(, -6).Resize(, 7).Clear
            .Cells(1, i - 6).Resize(dic.Count, 7) = arrOut
        Next
        
    End With

End Sub

I would be uneasy about trying out something that could trash all the data in a worksheet without a backup. in fact, I would normally write something like this to read from one sheet and write to another.

It continues your idea of using a dictionary to get a unique list of keys but it saves the row number as it is doing it.
It also reads in the whole block of seven columns into the same array.
When the data is transferred from the input array to the output array it uses only those rows that were saved in the dictionary.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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
Back
Top