VBA: Validación Datos con Registros Únicos

Una cuestión frecuentemente planteada es cómo conseguir listar empleando la herramienta de Validación de datos, registros únicos y sin vacíos.

Partiremos del siguiente rango de trabajo, donde se repiten en el tiempo para diferentes usuarios el empleo de diferentes aplicaciones del paquete Office de Microsoft, tal como se muestra en la imagen siguiente:

Se ve claramente como el rango B3:E10 está compuesto por diferentes elementos repetidos y otros vacíos. Con un problema añadido, y es que los datos están dispuestos en una matriz (cuando sabemos que la validación de datos solo admite trabajr sobre vectores-una fila o una columna-).

El objetivo es incorporar una validación de datos en la celda G2 que permita introducir, y que muestre, solo los elementos de manera única y de elementos no vacíos.

Así pues, trabajaremos una macro en nuestro Excel.
Nuestra macro la creamos en un módulo estándar de nuestro libro de trabajo.
El código:

Sub ListaValidacionCelda()
Dim rngAplicaciones As Range
Set rngAplicaciones = Range("B3:E10")

Set apps = New Collection
'recorremos el rango
For Each celda In rngAplicaciones
    'nos centramos solo en celdas con valor...
    If celda.Value <> "" Then
        'identificamos valores únicos!!
        On Error Resume Next
        apps.Add celda.Value, CStr(celda.Value)
        On Error GoTo 0
    End If
Next celda

'trasladamos las aplicaciones únicas a una Array
Dim arr As Variant
ReDim arr(1 To apps.Count) As Variant
For i = 1 To apps.Count
    arr(i) = apps(i)
Next i
'para poder generar nuestra validación de celdas
With Range("G2").Validation
    .Delete
    .Add Type:=xlValidateList, _
           AlertStyle:=xlValidAlertStop, _
           Operator:=xlEqual, _
           Formula1:=Join(arr, ",")
End With
'liberamos memoria
Set rngAplicaciones = Nothing
End Sub

La magia del procedimiento se basa en tres puntos:
1- al pasar los datos por una Collection, nos aseguramos un listado de registros únicos... además, han pasado un filtro previo con el condicional de no vacíos.
2- generamos una Array con los elementos de la Collection. Es un paso necesario para poder realizar el tercer paso.
3- con la función Join unimos los elementos de la Array, con un separador de 'coma'... Cadena que añadimos a la herramienta de Validación.

Tras ejecutar nuestro procedimiento, el resultado lo observamos en la celda G2 comentada:

Consiguiendo nuestra meta.

Como curiosidad, si accedemos a la configuración de la validación de datos de nuestra celda G2, veremos la lista generada: