[Collection 개체를 이용해 중복값 찾기]

[Collection 개체를 이용해 중복값 찾기]

Option Explicit

Sub dblName()

Dim rngName As Range

Dim rngCell As Range

Dim cltName As New Collection

Dim varName As Variant

Dim i As Integer

'기존항목 제거 후 제목 입력

With Range("C1")

.CurrentRegion.ClearContents

.Value = "중복이름"

End With

'반편성 영역 지정

Set rngName = Range("A1").CurrentRegion

'중복값을 Collection에 저장

On Error Resume Next

For Each rngCell In rngName

If WorksheetFunction.CountIf(rngName, rngCell) > 1 Then

cltName.Add rngCell, CStr(rngCell)

End If

Next rngCell

On Error GoTo 0

'Collection에 저장된 중복값을 배열로 뿌림

For Each varName In cltName

Range("C2").Offset(i, 0) = varName

i = i + 1

Next varName

End Sub