Create range of duplicates from another range

[dpm]

Is it possible to create a range consisting of all cells with duplicate values contained in a different range?

Conversely, is it possible to remove all non-duplicated cells from a range?

I foolishly posed this question at the tail end of a post regarding another matter, so I'm creating a new thread now.

Thanks for any help!

 

[chergh]

The short answer is yes you can create a collection and use the cell value as the key and use that to get rid of the duplicates. I'll post a sample later if I have a bit more time.

 

[dpm]

Thank you very much. In the meantime, I'll be taking on the task you describe.

Cheers.

 

[chergh]

Something like this should work:

Public Function BigRange(Startrange As Range, Criteria As String) As Range
Dim Fst As Range
Dim FoundAll As Range
Dim F As Range

Dim rngColl As Collection
Dim rng As Range

Set rngColl = New Collection

    Set Fst = Startrange.Find(Criteria)
    
    rngColl.Add Fst.Offset(0, 1), CStr(Fst.Offset(0, 1).Value)

    Set F = Fst
    
    If Not F Is Nothing Then
    
        Do
    
            Set F = Startrange.FindNext(F)
            
        On Error Resume Next

        If F.Address <> Fst.Address Then
        
        
            rngColl.Add F.Offset(0, 1), CStr(F.Offset(0, 1).Value)

        End If
    
        On Error GoTo 0

        Loop While F.Address <> Fst.Address
    
    End If
   
    
    For Each rng In rngColl

        If FoundAll Is Nothing Then
        
            Set FoundAll = rng
            
        Else
        
            Set FoundAll = Union(FoundAll, rng)
            
        End If
        
        Next rng

    Set BigRange = FoundAll
            
    
End Function

 

[dpm]

Thanks very much. I'll experiment with this. Looks like it should do what I need.