top of page

Certainly! Here's an example Excel VBA macro that achieves what you're looking for:

Sub CreateDepartmentDropdown()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim deptColumn As ListColumn
    Dim deptRange As Range
    Dim uniqueDepts As Collection
    Dim dept As Variant
    Dim i As Long
    ' Set the worksheet containing the table
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    ' Set the table
    Set tbl = ws.ListObjects("Data") ' Change "Data" to your table's name
    ' Set the Department column
    Set deptColumn = tbl.ListColumns("Department") ' Change "Department" to your column's name
    ' Initialize collection to store unique departments
    Set uniqueDepts = New Collection
    ' Loop through each cell in the Department column
    On Error Resume Next
    For Each deptRange In deptColumn.DataBodyRange
        uniqueDepts.Add deptRange.Value, CStr(deptRange.Value)
    Next deptRange
    On Error GoTo 0
    ' Sort the unique departments alphabetically
    On Error Resume Next
    On Error GoTo 0
    ' Clear existing validation and add new dropdown validation
    With ws.Range("SupDep").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=JoinCollection(uniqueDepts, ",")
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Function JoinCollection(col As Collection, delimiter As String) As String
    Dim item As Variant
    Dim result As String
    For Each item In col
        If result <> "" Then
            result = result & delimiter
        End If
        result = result & item
    Next item
    JoinCollection = result
End Function

Please make sure to customize the following parts of the code to match your actual data:

1. `Sheet1`: Replace with the name of your worksheet.
2. `Data`: Replace with the name of your table.
3. `Department`: Replace with the name of your department column.
4. `SupDep`: Replace with the address of the cell where you want the dropdown to appear.

To add and run the macro, press `ALT + F11` to open the VBA editor, then insert a new module and paste the code there. You can then run the macro by pressing `F5` or by going to `Run > Run Sub/UserForm` in the VBA editor.

bottom of page