Selecting Duplicates and Inverting the Selection! (Excel 2000 >)

Topic

New Reply

This Q is not for work purposes…its just to learn and experiment on!

I have not been able to find out how to select all duplicates in a bunch of numbers, (as in multiple select!) Also, is it possible to show me how this selection of multiple cells can be inverted so that all non duplicated values get selected. I assume a NOT operator will be involved here. The code in the sample W/B is a start!!!!

TIA

Reply | Quote

Replies

Rudi

I shall start by giving you some code which you can adapt that finds duplicates in a column and then a function that will allow you to sort the colour formatted fields using A-Z. At present this only works for a single column. this came from post 484000

Sub FindDups()
‘
‘ NOTE: You must select the first cell in the column and
‘ make sure that the column is sorted before running this macro
‘
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell “”
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

——————————————————————————————–
Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer

Dim C As Long
If OfText = True Then
C = Rng.Font.ColorIndex
Else
C = Rng.Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Rng.Worksheet.Parent)
Else
C = GetWhite(Rng.Worksheet.Parent)
End If
End If

Reply | Quote

Thanx for the time to search for this code Jezza. However, this is not what i need. I have been frustrated by how a person selects multiple cells that meet a certain criteria. The use of special cell is easy in code, and if I record a macro to select multiple cells, the recorder records Range(“A1”, “B6:D10″,”C20”).Select! This is what i am after.
Thanx again…please know that i appreciate the reply!

Reply | Quote

Alternatively

In cell H2 paste =IF(COUNTIF($B$2:B2,B2)>1,”Duplicate”,”Unique”)

in cell I2 paste =IF(COUNTIF($C$2:C2,C2)>1,”Duplicate”,”Unique”)
etc to M2

copy down to Row 22

You will then have a grid of Duplicate and Unique. You can then sort on thhes two values and show all your duplicates grouped together

Reply | Quote

Here’s a different take. The following procedure will select all cells containing duplicate values:

Sub SelectDups()
Dim oRange As Range
Dim oCell As Range
Dim oSelect As Range
Set oRange = Range(“B2”).CurrentRegion
For Each oCell In oRange.Cells
If Application.WorksheetFunction.CountIf(oRange, oCell) > 1 Then
If oSelect Is Nothing Then
Set oSelect = oCell
Else
Set oSelect = Union(oSelect, oCell)
End If
End If
Next oCell
oSelect.Select
Set oSelect = Nothing
Set oCell = Nothing
Set oRange = Nothing
End Sub

If you change > 1 to = 1, the code will select the cells containing unique values instead.

Reply | Quote

Hans,
This is what i was after. I see that the code loops through each cell, testing it with the CountIf function. Then after it has evaluated the cells it uses the Union Function to select all the ranges! I looped through the macro to watch the variable values. i see it only tests the first duplicate. (Ie Set oSelect = oCell only shows 59, which is the first duplicate.) How does the code find the other duplicates, as I do not see the cell values passed through the oCell variable?
Also, i guess the secret to selecting various cells is getting a reference to them and using the Union Function to select them all!
This is neat!
Tx

Reply | Quote

Some comments on the central part of the code:

Loop through the cells
For Each oCell In oRange.Cells
Do we have a duplicate? (This mimics the condition you used for conditional formatting)
If Application.WorksheetFunction.CountIf(oRange, oCell) > 1 Then
Has oSelect been filled yet?
If oSelect Is Nothing Then
If not, i.e. oCell is the first duplicate encountered, set oSelect to equal oCell (as a range)
Set oSelect = oCell
Else
Otherwise, add oCell to oSelect
Set oSelect = Union(oSelect, oCell)
End If
End If
Next oCell

Reply | Quote

Thanx. this code is definetly going into my Useful Code List. This can be modified to select anything and do with it as needed!
Great Post!!!!!!!

This code can be called: CONDITIONAL SELECTION

Reply | Quote

Rudi:
A comprehensive answer to the problem you raised can be found in “Writing Excel Macros” Authored by Steven Roman Published by O’Reilly. In addition the book is a good Excel VBA guide which I have used on several occasions to enhance my Excel VBA procedures.

I am posting this mostly for the benefit of other Loungers that may read this post.

Regards,
Tom Duthie

Reply | Quote

Thanx Tom.

Reply | Quote

Well knock me down with a feather….. [make note to myself ” Look up Union function when I have 5 minutes”]

Reply | Quote