Delete non duplicate rows

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360
Greetings folks,

I have a spreadsheet that contains many rows of data. Some of the cell values in column A are duplicates while others are unique and there is only 1. I would like to use VBA to delete the rows that contain unique values in column A, while leaving the duplicate values (+ their corresponding rows) found in column A.

Any assistance would be greatly appreciated.

C
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360
Fluff...you have been very helpful and I've asked lots of questions...

Along the line of duplicates, I have found the following code that uses specific colours to highlight duplicates. Is there a way to apply it to a range such as A2:D150?

VBA Code:
Dim a, i As Long
a = Range("A2", Range("a" & Rows.Count).End(xlUp)).Value
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 1 To UBound(a, 1)
    If Not IsEmpty(a(i, 1)) Then
        If Not .exists(a(i, 1)) Then .Item(a(i, 1)) = VBA.Array("", 0)
        w = .Item(a(i, 1))
        w(0) = w(0) & IIf(w(0) = "", "", ",") & Cells(i, 1).Address(0, 0)
        w(1) = w(1) + 1: .Item(a(i, 1)) = w
    End If
Next
For Each e In .items
    If e(1) <> 1 Then
        Select Case e(1)
            Case 2: myClr = vbYellow
            Case 3: myClr = vbRed
            Case 4: myClr = vbBlue
            Case 5: myClr = vbMagenta
            Case 6: myClr = vbGreen
            Case Else: myClr = vbCyan
        End Select
        Range(e(0)).Interior.Color = myClr
    End If
Next
End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
As this is now a totally different question, please start a new thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,194
Messages
5,546,487
Members
410,742
Latest member
WalterSil
Top