Excel VBA로 데이터 유효성 검사 목록에 이름 정의 범위(이름 있는 범위) 활용하는 4가지 방법

Excel VBA에서 데이터 유효성 검사 목록에 이름 정의 범위(named range)를 가장 손쉽게 활용하는 방법을 찾고 계신다면, 이 글이 큰 도움이 될 것입니다. 이름 정의 범위는 드롭다운 목록을 간편하게 만들 수 있도록 데이터 유효성 검사 수식에 활용되며, 몇 줄의 VBA 코드만으로도 이 작업을 훨씬 더 쉽게 처리할 수 있습니다.

그럼 지금부터 데이터 유효성 검사 목록에 이름 정의 범위를 사용하는 다양한 방법을 하나씩 살펴보겠습니다.

워크북 다운로드

Excel VBA로 데이터 유효성 검사 목록에 이름 정의 범위를 사용하는 4가지 방법

여기서는 여러 제품과 해당 판매자 목록이 담긴 아래와 같은 데이터셋을 사용합니다. 이 데이터셋을 바탕으로 서로 다른 VBA 코드를 활용해 데이터 유효성 검사 목록에 이름 정의 범위를 적용하는 다양한 방법을 소개하겠습니다.

본 글은 Microsoft Excel 365 버전 기준으로 작성되었으며, 편의에 따라 다른 버전을 사용하셔도 무방합니다.

방법 1: 이름 정의 범위를 활용해 드롭다운 목록 만들기

여기서는 Fruits(과일) 열의 범위에 Fruits라는 이름을 미리 지정해 두었고, VBA 코드를 사용해 D6 셀에 드롭다운 목록을 만들어 보겠습니다.

1단계:
➤ 개발 도구(Developer) 탭 >> Visual Basic 옵션으로 이동합니다.

그러면 Visual Basic 편집기가 열립니다.
➤ 삽입(Insert) 탭 >> 모듈(Module) 옵션을 선택합니다.

이제 새로운 모듈이 생성됩니다.

2단계:
➤ 아래 코드를 입력합니다.

Sub Datavalidation1()

Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"

End Sub

여기서 Validation은 D6 셀에 추가되고, xlValidateList는 드롭다운 목록을 생성하기 위한 설정이며, 수식에는 범위 이름인 "=Fruits"가 사용됩니다.

➤ F5 키를 눌러 실행한 뒤, D6 셀의 드롭다운 화살표를 클릭합니다.
그러면 과일 목록이 나타나고, 목록에서 원하는 항목(예: Cherries)을 선택할 수 있습니다.

최종적으로 선택한 항목이 D6 셀에 표시됩니다.

더 읽어보기: Excel에서 표(Table)로 데이터 유효성 검사 목록 만드는 방법 (3가지)

방법 2: VBA 코드로 이름 정의 범위와 데이터 유효성 검사 목록 한 번에 추가하기

이번에는 이름 정의 범위를 직접 만들지 않고, 간단한 VBA 코드가 자동으로 범위 이름을 생성한 후 이를 활용해 최종적으로 D6 셀에 드롭다운 목록을 완성하는 방법을 살펴보겠습니다.

단계:
➤ 방법 1의 1단계를 따릅니다.
➤ 아래 코드를 입력합니다.

Sub Datavalidation2()

ActiveWorkbook.Names.Add Name:="Fruit", _
RefersTo:=ThisWorkbook.Worksheets("Add").Range("B4:B10")
Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruit"

End Sub

먼저 Add 워크시트의 "B4:B10" 범위에 Fruit라는 이름이 지정됩니다.
그런 다음 D6 셀에 Validation이 추가되고, xlValidateList는 드롭다운 목록 생성을 위한 설정이며, 수식에는 범위 이름인 "=Fruit"가 사용됩니다.

➤ F5 키를 누른 후 워크시트로 돌아가 D6 셀의 드롭다운 화살표를 클릭합니다.
이후 과일 목록이 나타나며, 목록에서 원하는 항목(예: Blueberries)을 선택하면 됩니다.

이렇게 원하는 항목인 Blueberries를 목록에서 얻었고, 동시에 과일 범위에 대해 생성된 이름 정의 범위도 확인할 수 있습니다.

관련 콘텐츠: Excel에서 다중 선택이 가능한 데이터 유효성 검사 드롭다운 목록 만들기

함께 보면 좋은 글:

• Excel에서 데이터 유효성 검사 드롭다운 목록 자동완성 기능 활용하기 (2가지 방법)
• 필터가 적용된 Excel 데이터 유효성 검사 드롭다운 목록 (2가지 예제)
• Excel VBA로 데이터 유효성 검사 목록의 기본값 설정하기 (매크로 및 사용자 폼)
• Excel에서 여러 조건에 맞는 사용자 지정 데이터 유효성 검사 적용하기 (4가지 예제)
• Excel 데이터 유효성 검사에서 영숫자만 허용하기 (사용자 지정 수식 활용)

방법 3: Excel VBA로 이름 정의 범위를 사용해 데이터 유효성 검사 목록 자동 업데이트하기

예를 들어 D6 셀에 아래와 같은 드롭다운 목록이 있다고 가정해 보겠습니다. 고정된 데이터셋에서는 잘 작동하지만,

새로운 채소 Lettuce(상추)를 추가하면 드롭다운 목록에 나타나지 않습니다. 즉, 이 경우 드롭다운 목록이 자동으로 업데이트되지 않는다는 뜻입니다.

목록을 빠르고 자동으로 업데이트하려면 아래 방법을 따라 해 보세요.

3.1: 자동 업데이트되는 이름 정의 범위 만들기

먼저 B열의 범위에 이름을 지정하되, 새로 추가되는 항목이 자동으로 포함되도록 설정해야 합니다.
➤ 수식(Formulas) 탭 >> 정의된 이름 그룹 >> 이름 관리자(Name Manager) 옵션으로 이동합니다.

그러면 이름 관리자 대화 상자가 열립니다.
➤ 새로 만들기(New)를 클릭합니다.

이어서 새 이름 마법사가 나타납니다.
➤ 이름 상자에 Vegetables를 입력하고, 참조 대상 상자에 아래 수식을 입력한 후 확인(OK)을 누릅니다.

여기서 Update!는 시트 이름이고, $B$4는 기준이 되는 참조 셀이며, 행(Rows)과 열(Columns) 인수가 모두 0이므로 시작 위치 그대로 유지됩니다.
COUNTA 함수는 B열에서 값이 있는 셀의 개수를 세고, B1의 데이터셋 제목과 B3의 열 머리글 때문에 2를 뺍니다. 결과적으로 실제 채소가 들어 있는 셀의 개수만 구하게 됩니다.
이 숫자가 시작 위치로부터의 반환 참조 범위가 되며, OFFSET 함수 덕분에 이름 정의 범위는 항상 최신 상태로 유지됩니다.

이제 이름 관리자 창으로 돌아갑니다.
➤ 닫기(Close)를 클릭합니다.

3.2: VBA 코드로 데이터 유효성 검사 목록 적용하기

➤ 시트 이름에서 마우스 오른쪽 버튼을 클릭하고 코드 보기(View Code) 옵션을 선택합니다.

그러면 코드 창이 나타납니다.

➤ 아래 코드를 입력합니다.

Sub worksheet_Change(ByVal newitem As Range)

Dim updatedrange, item
If Not Intersect(newitem, Range("B:B")) Is Nothing Then
For Each item In Range("Vegetables")
updatedrange = updatedrange & "," & item
Next item
With ActiveSheet.Range("D6").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:=updatedrange
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
End With
End If

End Sub

이 코드는 값이 변경되거나 새 항목이 추가될 때만 실행되므로 프로시저를 Worksheet_Change로 정의했습니다. 여기서 Worksheet는 개체(Object)이고, Change는 프로시저(Procedure)입니다.
newitem은 새 값을 입력하는 셀의 주소를 담고 있으며 Range로 선언했습니다. updatedrange와 item의 데이터 형식은 Variant로 처리되며, updatedrange에는 업데이트된 채소 이름 정의 범위가, item에는 해당 범위 내 각 셀의 값이 할당됩니다.
FOR 루프가 업데이트된 범위를 updatedrange에 할당하고, WITH 문은 동일한 개체의 반복 입력을 피하게 해주며, 마지막으로 유효성 검사(validation)를 추가합니다.

이제 메인 시트로 돌아가 Lettuce를 추가했을 때 어떤 변화가 생기는지 확인해 보겠습니다.
보시는 것처럼 새 항목이 드롭다운 목록에 바로 나타납니다.

이 새 항목을 선택하면 D6 셀에 표시됩니다.

더 읽어보기: 다른 시트의 데이터로 유효성 검사 목록 사용하는 방법 (6가지)

방법 4: 이름 정의 범위를 활용해 조건부 드롭다운 목록 만들기

이번에는 D6 셀의 값에 따라 달라지는 드롭다운 목록을 E6 셀에 만들어 보겠습니다. 이를 위해 fruit1과 vegetable1이라는 두 개의 이름 정의 범위를 준비했습니다.

단계:
➤ 방법 1의 1단계를 따릅니다.
➤ 아래 코드를 입력합니다.

Sub Datavalidation4()

If Range("D6") = "Fruits" Then
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=fruit1"
Else
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=vegetable1"
End If

End Sub

IF-THEN 문이 D6 셀의 값이 Fruits인지 확인하고, 그렇다면 E6 셀에 이름 정의 범위 fruit1이 목록으로 표시되며, 그렇지 않으면 이름 정의 범위 vegetable1이 목록으로 표시됩니다.

➤ F5 키를 누른 후 워크시트로 돌아가 E6 셀의 드롭다운 화살표를 클릭합니다.
D6 셀의 범주가 Fruits이므로 과일 목록이 나타나고, 목록에서 원하는 항목(예: Blackberries)을 선택하면 됩니다.

이렇게 원하는 항목인 Blackberries를 목록에서 얻었습니다.

➤ 범주를 Vegetables로 변경한 후 코드를 다시 실행하면, 채소 목록이 표시되는 것을 확인할 수 있습니다.

Broccoli를 선택하면 해당 항목이 E6 셀에 나타납니다.

관련 콘텐츠: Excel 데이터 유효성 검사 수식에서 IF 문 활용하는 방법 (6가지)

연습 섹션

직접 연습해 볼 수 있도록 Practice라는 이름의 시트에 아래와 같은 연습 섹션을 마련해 두었습니다. 스스로 실습해 보시기 바랍니다.

결론

이번 글에서는 Excel VBA에서 이름 정의 범위를 데이터 유효성 검사 목록에 손쉽게 활용하는 다양한 방법을 다루었습니다. 도움이 되었기를 바랍니다. 제안이나 궁금한 점이 있다면 댓글로 자유롭게 남겨주세요.

관련 글

• Excel에서 색상을 활용해 데이터 유효성 검사 사용하는 방법 (4가지)
• Excel VBA로 배열(Array)에서 데이터 유효성 검사 목록 만들기
• Excel 데이터 유효성 검사에서 사용자 지정 VLOOKUP 수식 활용하기
• [해결됨] Excel에서 복사-붙여넣기 시 데이터 유효성 검사가 작동하지 않는 문제 (해결책 포함)
• Excel 데이터 유효성 검사 목록에서 빈 셀 제거하는 방법 (5가지)