Excel의 VBA를 사용한 드롭다운 목록의 고유 값(전체 가이드)

이 문서에서는 드롭다운 목록에서 중복 값을 제거하는 방법을 보여 드리겠습니다. VBA의 도움으로 Excel의 워크시트 고유한 값만 유지합니다. 적어도 한 번과 정확히 한 번 모두 나타나는 고유한 값을 추출하는 방법을 배우게 됩니다.

Excel VBA를 사용한 드롭다운 목록의 고유 값(빠른 보기)

Sub Drop_Down_List_Unique_Values_At_Least_Once()

List_Location = "B3"

Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")

Range(List_Location).Validation.Delete

Unique_Data = ""

Count = 0

For i = LBound(Data) To UBound(Data)
    Unique_Values = Split(Unique_Data, ",")
    For j = LBound(Unique_Values) To UBound(Unique_Values)
        If Data(i) = Unique_Values(j) Then
            Count = 1
            Exit For
        End If
    Next j
    If Count = 0 Then
        If Unique_Data = "" Then
            Unique_Data = Unique_Data + Data(i)
        Else
            Unique_Data = Unique_Data + "," + Data(i)
        End If
    End If
    Count = 0
Next i

Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data

End Sub

Excel VBA를 사용하여 드롭다운 목록에서 고유한 값을 유지하는 방법

B3 셀에 드롭다운 목록이 있습니다. 일부 국가의 이름이 포함된 Excel 워크시트의 일부입니다.

그러나 보시다시피 목록에서 일부 이름이 반복되었습니다. 독일이 세 번, 이탈리아가 두 번 반복된 것처럼.

오늘의 목표는 드롭다운 목록에서 중복 값을 제거하고 고유한 값만 유지하는 것입니다.

1. 최소 한 번은 나타나는 드롭다운 목록에 고유한 값을 유지하는 매크로 개발

우선 매크로 드롭다운 목록에 한 번 이상 표시되는 고유한 값을 유지합니다.

예를 들어 위에서 언급한 목록의 경우 매크로 독일, 이탈리아, 프랑스, ​​영국이 됩니다. .

VBA 이 목적을 위한 코드는 다음과 같습니다:

⧭ VBA 코드:

Sub Drop_Down_List_Unique_Values_At_Least_Once()

List_Location = "B3"

Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")

Range(List_Location).Validation.Delete

Unique_Data = ""

Count = 0

For i = LBound(Data) To UBound(Data)
    Unique_Values = Split(Unique_Data, ",")
    For j = LBound(Unique_Values) To UBound(Unique_Values)
        If Data(i) = Unique_Values(j) Then
            Count = 1
            Exit For
        End If
    Next j
    If Count = 0 Then
        If Unique_Data = "" Then
            Unique_Data = Unique_Data + Data(i)
        Else
            Unique_Data = Unique_Data + "," + Data(i)
        End If
    End If
    Count = 0
Next i

Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data

End Sub

⧭ 출력:

코드를 실행합니다. B3 셀의 드롭다운 목록에서 중복 값을 제거합니다. 활성 워크시트의 일부를 제거하고 한 번 이상 나타나는 값만 유지합니다.

⧭ 참고:

코드를 실행하기 전에 드롭다운 목록으로 워크시트를 활성화하는 것을 잊지 마십시오. 또한 코드를 실행하기 전에 필요에 따라 목록 위치의 셀 참조를 변경하십시오.

자세히 알아보기: Excel에서 고유한 값으로 드롭다운 목록을 만드는 방법(4가지 방법)

2. 한 번만 나타나는 드롭다운 목록의 고유 값을 유지하는 매크로 만들기

이번에는 매크로 드롭다운 목록에 정확히 한 번 나타나는 고유한 값을 유지합니다.

예를 들어 위에서 언급한 목록의 경우 매크로 프랑스, 영국이 됩니다. .

VBA 이 목적을 위한 코드는 다음과 같습니다:

⧭ VBA 코드:

Sub Drop_Down_List_Unique_Values_Exactly_Once()

List_Location = "B3"

Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")

Unique_Data = ""

Range(List_Location).Validation.Delete

Count = 0

For i = LBound(Data) To UBound(Data)
    For j = LBound(Data) To UBound(Data)
        If j <> i And Data(i) = Data(j) Then
            Count = 1
            Exit For
        End If
    Next j
    If Count = 0 Then
        If Unique_Data = "" Then
            Unique_Data = Unique_Data + Data(i)
        Else
            Unique_Data = Unique_Data + "," + Data(i)
        End If
    End If
    Count = 0
Next i

Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data

End Sub

⧭ 출력:

코드를 실행합니다. B3 셀의 드롭다운 목록에서 중복된 값이 제거됩니다. 활성 워크시트에서 정확히 한 번 나타나는 값만 유지합니다.

⧭ 참고:

코드를 실행하기 전에 드롭다운 목록으로 워크시트를 활성화하는 것을 잊지 마십시오. 또한 코드를 실행하기 전에 필요에 따라 목록 위치의 셀 참조를 변경하십시오.

관련 콘텐츠: Excel의 드롭다운 목록에서 다중 선택하는 방법(3가지 방법)

유사한 수치:

• Excel에서 선택 항목을 기반으로 데이터를 추출하는 드롭다운 필터 만들기
• 색상으로 Excel 드롭다운 목록을 만드는 방법(2가지 방법)
• Excel 드롭다운 목록이 작동하지 않음(8가지 문제 및 해결 방법)
• Excel의 자동 업데이트 드롭다운 목록(3가지 방법)
• Excel의 드롭다운 목록에서 값을 선택하는 VBA(2가지 방법)

3. 드롭다운 목록에 고유한 값을 넣는 UserForm 개발

마지막으로 UserForm 드롭다운 목록에서 중복 값을 제거하고 VBA가 있는 고유한 값만 유지하려면 .

⧪ 1단계:사용자 양식 열기

삽입> 사용자 양식으로 이동합니다. VBA의 옵션 새 UserForm을 여는 편집기 . 새로운 사용자 양식 UserForm1이라고 함 열릴 것입니다.

⧪ 2단계:도구를 UserForm으로 드래그

UserForm 외에 , 도구 상자가 표시됩니다. . 도구 상자 위로 커서를 이동합니다. 라벨 3개 드래그 및 2개의 목록 상자 (Label1 아래 및 Label3 ) 및 1 텍스트 상자 (레이블2 아래 ) 그림과 같이 표시됩니다.

마지막으로 CommandButton 오른쪽 하단 모서리로 이동합니다.

⧪ 3단계:ListBox1용 코드 작성

ListBox1을 두 번 클릭합니다. . 비공개 하위 절차 ListBox1_Click이라고 함 열릴 것이다. 거기에 다음 코드를 입력하세요.

Private Sub ListBox1_Click()

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Worksheets(UserForm1.ListBox1.List(i)).Activate
        Exit For
    End If
Next i

End Sub

⧪ 4단계:TextBox1용 코드 작성

그런 다음 TextBox1을 두 번 클릭합니다. . 또 다른 비공개 하위 절차 TextBox1_Change라고 함 열릴 것이다. 거기에 다음 코드를 입력하세요.

Private Sub TextBox1_Change()

On Error GoTo TB1:

ActiveSheet.Range(UserForm1.TextBox1.Text).Select

Exit Sub

TB1:
    x = 21

End Sub

⧪ 6단계:CommandButton1용 코드 작성

마지막으로 CommandButton1을 두 번 클릭합니다. . 비공개 하위 절차 CommandButton1_Click이라고 함 열릴 것이다. 거기에 다음 코드를 입력하세요.

Private Sub CommandButton1_Click()

List_Location = UserForm1.TextBox1.Text

Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")

Range(List_Location).Validation.Delete

Unique_Data = ""

Count = 0

If UserForm1.ListBox2.Selected(0) = True Then
    For i = LBound(Data) To UBound(Data)
        Unique_Values = Split(Unique_Data, ",")
        For j = LBound(Unique_Values) To UBound(Unique_Values)
            If Data(i) = Unique_Values(j) Then
                Count = 1
                Exit For
            End If
        Next j
        If Count = 0 Then
            If Unique_Data = "" Then
                Unique_Data = Unique_Data + Data(i)
            Else
                Unique_Data = Unique_Data + "," + Data(i)
            End If
        End If
        Count = 0
    Next i

ElseIf UserForm1.ListBox2.Selected(1) = True Then
    For i = LBound(Data) To UBound(Data)
        For j = LBound(Data) To UBound(Data)
            If j <> i And Data(i) = Data(j) Then
                Count = 1
                Exit For
            End If
        Next j
        If Count = 0 Then
            If Unique_Data = "" Then
                Unique_Data = Unique_Data + Data(i)
            Else
                Unique_Data = Unique_Data + "," + Data(i)
            End If
        End If
        Count = 0
    Next i
Else
    MsgBox "Select Either At Least Once or Exactly Once.", vbExclamation
End If

Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data

End Sub

⧪ 7단계:UserForm 실행을 위한 코드 작성

새 모듈 삽입 VBA 도구 모음에서 거기에 다음 코드를 삽입하세요.

Sub Run_UserForm()

UserForm1.Caption = "Keep Unique Values in Drop-Down List"

UserForm1.Label1.Caption = "Worksheet: "
UserForm1.Label2.Caption = "List Location: "
UserForm1.Label3.Caption = "Keep Unique Values that Appear: "

UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption

For i = 1 To Sheets.Count
    UserForm1.ListBox1.AddItem Sheets(i).Name
Next i

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
        UserForm1.ListBox1.Selected(i) = True
        Exit For
    End If
Next i

UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.ListStyle = fmListStyleOption

UserForm1.ListBox2.AddItem "At Least Once"
UserForm1.ListBox2.AddItem "Exactly Once"

UserForm1.CommandButton1.Caption = "OK"

Load UserForm1
UserForm1.Show

End Sub

⧪ 8단계:UserForm 실행(최종 출력)

사용자 양식 이제 사용할 준비가 되었습니다. 매크로 실행 Run_UserForm이라고 함 .

사용자 양식 워크시트에 로드됩니다.

드롭다운 목록이 있는 워크시트를 선택합니다. Sheet3입니다. .

그런 다음 워크시트에서 목록 위치의 셀 참조를 입력합니다. B3입니다. .

마지막으로 최소 한 번을 선택합니다. 또는 정확히 한 번 여기에서 적어도 한 번을 선택했습니다. .

그래서 내 UserForm 이렇게 생겼습니다:

그런 다음 확인을 클릭합니다. . 선택한 기준에 따라 입력 위치의 드롭다운 목록에서 중복 값이 ​​제거됩니다.

자세히 알아보기: Excel에서 수식을 기반으로 드롭다운 목록을 만드는 방법(4가지 방법)

기억해야 할 사항

• 이 기사에서는 드롭다운 목록에서만 중복 값을 제거하는 데 중점을 두었습니다. 드롭다운 목록을 만드는 방법이나 중복 목록에서 값을 정렬하는 방법을 배우고 싶다면 이 기사를 읽어보세요.

결론

따라서 다음은 Excel VBA를 사용하여 드롭다운 목록에서 중복 값을 제거하고 고유한 값만 뒤에 유지하는 방법입니다. . 질문있으세요? 언제든지 문의해 주세요. ExcelDemy 사이트를 방문하는 것도 잊지 마세요. 더 많은 게시물과 업데이트를 확인하세요.

관련 기사

• Excel에서 셀 값을 드롭다운 목록과 연결하는 방법(5가지 방법)
• Excel의 조건부 드롭다운 목록(생성, 정렬 및 사용)
• Excel에서 동적 종속 드롭다운 목록을 만드는 방법
• IF 문을 사용하여 Excel에서 드롭다운 목록을 만드는 방법
• Excel의 드롭다운 목록이 있는 VLOOKUP