VBA: Encontrar Celdas No Vacías

Hoy veremos una forma de localizar las celdas NO vacías que se encuentren en nuestro rango, y en cualquier posición que nos interese.

Construiremos una sencilla función personalizada con VBA (una UDF) que recorrerá nuestro rango, guardándose las posiciones de las celdas vacías, para luego retornarlas a petición.

Nuestro procedimiento Function lo insertamos en un módulo estándar de nuestro proyecto de VBA.

Function PosVacia(rng As Range, pos As Long) As String
Application.Volatile
Dim ArrVacias() As Variant
vacias = Application.WorksheetFunction.CountBlank(rng)
'redefinimos la matriz
ReDim ArrVacias(1 To vacias) As Variant

'recorremos las celdas marcadas (las vacías)
x = 1
For Each celda In rng
    'cuando la celdas esté vacía/sin dato
    'entonces la cargamos a nuestra matriz
    If celda.value = "" Then
        ArrVacias(x) = celda.Address
        x = x + 1
    End If
Next celda

'devolvemos el valor a la función
PosVacia = ArrVacias(pos)
End Function

Una alternativa con un procedimiento Sub, sería empleando el método .SpecialCells, en concreto con el parámetro:
xlCellTypeBlanks

Vemos cómo quedaría.

Sub PosicionVacia()
pos = 2     'posición deseada
Set rng = Range("B2:B13")   'rango donde localizar las celdas vacías.
Dim ArrVacias() As Variant
Dim vacias As Range
Set vacias = rng.SpecialCells(xlCellTypeBlanks)

'redefinimos la matriz
ReDim ArrVacias(1 To vacias.Count) As Variant
'recorremos las celdas marcadas (las vacías)
x = 0
For Each celda In vacias
        'entonces la cargamos a nuestra matriz
    x = x + 1
    ArrVacias(x) = celda.Address
Next celda
'devolvemos la dirección de la celda con un MsgBox
MsgBox ArrVacias(pos)
End Sub

Esto me lleva, automáticamente a construir una función, variante de la primera, que sirva para localizar cualquier posición de cualquier valor dentro de un rango.

Así un nuevo procedimiento Function insertado en un módulo estándar de nuestro proyecto de VBA.

Function PosCelda(buscado As Variant, rng As Range, pos As Long) As String
Application.Volatile
Dim ArrCeldas() As Variant
contador = Application.WorksheetFunction.CountIf(rng, buscado)
'redefinimos la matriz
ReDim ArrCeldas(1 To contador) As Variant

'recorremos las celdas marcadas
x = 1
For Each celda In rng
    'cuando la celdas coincidan con el valor buscado
    'entonces la cargamos a nuestra matriz
    If celda.Value = buscado Then
        ArrCeldas(x) = celda.Address
        x = x + 1
    End If
Next celda

'devolvemos el valor a la función
PosCelda = ArrCeldas(pos)
End Function

Vemos que es muy similar al primero, pero ahora controlamos cuál es el valor buscado... para así recuperar la dirección de ese valor que se encuentre en la posición requerida...