VBA: Número de Filas usadas en Excel – Misión Imposible

Resulta extraño, y hasta paradójico, que una de las tareas más comunes que se suele implementar, mediante macros en Excel, sea, por decirlo de forma decente, «problemática».

Me refiero a calcular, mediante macros en VBA, la fila máxima con datos que tiene una columna en una hoja de Excel.

EL PROBLEMA

Consideremos el siguiente ejemplo el caso de una hoja Excel con la siguiente distribución:

Aquí, se desea calcular, usando VBA, la máxima fila usada para la columna A.

No hace falta ser muy observador, para darse cuenta que el resultado debe ser la fila 6, pero para demarcar bien el problema expongamos primero las consideraciones:

• CONSIDERACIÓN 1: Puede haber celdas vacías entre datos válidos en la columna (Como el caso de la celda A4).
• CONSIDERACIÓN 2: Puede haber filas ocultas, que incluyan o no a la última celda con datos. Por ejemplo pueden estar ocultas las filas de la 4 a la 7.
• CONSIDERACIÓN 3: Pueden haber filas filtradas, usando diversos campos. Por ejemplo pueden estar filtradas las filas 5 y 6.

Ahora ya no es tan fácil. ¿Verdad?

Estas condiciones consideran un caso bastante general, porque si el problema fuera tan sencillo como encontrar la máxima fila usada, en un caso sin celdas vacías, sin filas ocultas y sin filtros, realmente no me hubiera molestado en escribir este artículo.

ALTERNATIVAS

Existe en la web (¿dónde más?) diversos métodos de solución, para este problemas (o problemas parecidos), pero no he encontrado un método realmente eficiente (que no implique leer fila por fila), que pueda resolver por completo el problema.

Una de las soluciones más completas se puede encontrar en este portal:  https://exceltotal.com/encontrar-ultima-fila-vba/

Las soluciones allí planteadas son buenas pero, para nuestro caso, todas ellas fallan en algo. Analizemos las soluciones, para nuestro problema.

ENCONTRAR LA ÚLTIMA FILA CON LA PROPIEDAD «UsedRange»

Este método es rápido y simple, y consiste en usar la propiedad UsedRange. El código sería algo como:

Sub EncontrarÚltimaFila()
  Dim f As Long
  'Lee la fila de la ultima celda de UsedRange
  f = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
  MsgBox f
End Sub

Esta macro es equivalente (pero no exactamente) a encontrar la última celda, pulsando la combinación <Ctrl>+<End>

Podemos probar ejecutando la macro sobre nuestros datos:

Desgraciadamente esta macro solo funcionará si se tiene una sola columna en la hoja, ya que UsedRange, considera todas las columnas.

Consideremos por ejemplo el siguiente caso:

Este problema, muestra que UsedRange, está pensado para encontrar la celda más inferior y más a la derecha. No hay forma de indicarle a UsedRange, que trabaje en una columa o fila específica.

Otros de los problemas de UsedRange son:

1. El método SpecialCells(xlCellTypeLastCell), solo se actualiza cuando se abre o se guarda el libro. Lo cual podría hacer que arroje un valor mayor. Este problema, sin embargo, solo lo he observado cuando se usa <Ctrl>+<End>. Al ejecutar la macro, se obtiene el resultado correcto.
2. Las celdas con formato (aunque solo se pinten de blanco), son también consideradas por SpecialCells(xlCellTypeLastCell), para definir la última celda.
3. Las filas ocultas, son también consideradas por SpecialCells(xlCellTypeLastCell), aunque contengan solo celdas vacías.

Debido a este comportamiento  SpecialCells(xlCellTypeLastCell) podría dar un valor mayor o menor del valor buscado. En realidad, la mayoría de las veces daría un valor mayor, y solo podría dar un valor menor cuando haya filas ocultas con celdas no vacías.

Consideremos, por ejemplo,  ocultar las filas 5 y 6:

El valor que muestra SpecialCells(xlCellTypeLastCell), es la fila 4 porque ha encontrado celdas ocultas debajo de esa fila (no porque contengan datos). Lo mismo sucedería si se coultaran celdas sin datos:

En este caso se muestra la fila 8, porque se han ocultado las filas 9, 10 y 11 (que no tienen datos).

Lo que se demuestra aquí es que SpecialCells(xlCellTypeLastCell), considera a las filas ocultas, como si tuvieran datos, siempre, y como es de esperarse apuntará siempre a las últimas filas ocultas (a la celda anterior).

Si bien SpecialCells(xlCellTypeLastCell) no siempre nos dará la última celda con datos, si nos da una pista sobre donde está la máxima fila con datos (la siguiente celda visible).

Esta característica, nos servirá posteriormente para implementar un mejor método de búsqueda.

ENCONTRAR LA ÚLTIMA FILA CON LA PROPIEDAD «End»

Este método es equivalente a usar la combinación de teclas <Ctrl> + <direccional>.

Para nuestro problema podríamos usar la macro:

Sub EncontrarÚltimaFila()
  MsgBox ActiveSheet.Cells(1, "A").End(xlDown).Row
End Sub

Para buscar la última fila con datos, partiendo desde la celda superior, pero si quisiéramos tener en cuenta la CONSIDERACIÓN 1, es mejor buscar desde la celda final usando:

Sub EncontrarÚltimaFila()
  MsgBox ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
End Sub

Así se puede pasar por encima de las celdas vacías.

La ejecución de esta macro, sobre los datos de ejemplo, dará el resultado correcto:

A pesar de que este método es simple y funciona bien para celdas vacías, no cumple con las consideraciones CONSIDERACIÓN 2 y CONSIDERACIÓN 3.

Por ejemplo si se ocultan las filas 4, 5 y 6, se obtendrá un resultado erróneo:

Esto es debido a que End() no considera filas ocultas.

ENCONTRAR LA ÚLTIMA FILA CON EL MÉTODO «Find»

El método Find(), permite realizar búsquedas de diversas formas, y una de estas formas nos permitirá encontrar la última celda usada de un rango.

El truco consiste en realizar una búsqueda de cualquier texto sobre todo el rango de una columna, empezando desde la primera fila, en sentido inverso.

Así lo que se obtendrás será la última celda que contenga algún valor en ese rango.

Viéndolo en código sería así:

Sub EncontrarÚltimaFila()
  MsgBox ActiveSheet.Columns("A").Find("*", _
  searchorder:=xlByRows, searchdirection:=xlPrevious).Row
End Sub

Aplicando esta macro a la misma hoja de ejemplo tendríamos, también, el resultado correcto:

Y si aplicamos la macro al caso de la hoja con filas ocultas, veremos que también da el resultado correcto:

Entonces, se puede decir que este método, tiene un mejor comportamiento, porque cumple con la CONSIDERACIÓN 2, del problema.

Pero desgraciadamente, falla con la CONSIDERACIÓN 3. Veamos que pasa cuando filtramos filas:

El comportamiento de la macro, en este caso, es bastante mediocre, por así decirlo, así que no es la solución que estamos buscando.

LA «VIEJA CONFIABLE»

La forma más segura, pero a la vez la más ineficente, es la iteración bruta. Eso nunca falla. Pero tampoco es una solución elegante.

La macro implementada con este método se parecería a esta:

Sub EncontrarÚltimaFila()
Dim f As Long
  f = 65535 'Máximo valor de exploración
  While ActiveSheet.Cells(f, 1) = ""
    f = f - 1
  Wend
  MsgBox f
End Sub

Pongámosla a prueba para el caso con filas ocultas:

Y ahora para el caso con filas filtradas:

Y como vemos, trabaja excelentemente bien.

Pero si examinamos la macro veremos que se debe elegir una fila máxima de exploración, lo cual no es una solución muy «limpia», y si además se elige la fila máxima, se tendrá un desempeño muy pobre.

MI SOLUCIÓN

La solución que planteo para solucionar este menudo problema, no es tan simple e involucra varias fases, pero es de lejos, mejor que la exploración bruta.

La idea consiste en usar la propiedad UsedRange, para obtener una aproximación (la fila más inferior que puede contener datos) y luego ir puliendo la fila real, mediante exploraciones comunes.

Sub EncontrarÚltimaFila()
Dim lastCell As Range
Dim f As Long
Dim h As Worksheet
Dim c As Integer
  Set h = ActiveSheet 'Hoja de búsqueda
  c = 1 'Columna de búsqueda
  'Lee la fila de la ultima celda de UsedRange
  Set lastCell = h.UsedRange.SpecialCells(xlCellTypeLastCell)
  'Ubica lastCell en la columna
  Set lastCell = h.Cells(lastCell.Row, c)
  'Pasa a la siguiente celda visible.
  Set lastCell = lastCell.Offset(1, 0)
  While lastCell.EntireRow.Hidden = True
    Set lastCell = lastCell.Offset(1, 0)
  Wend
  'Pero puede que haya celdas sin datos antes
  While lastCell = ""
    Set lastCell = lastCell.Offset(-1, 0)
  Wend
  MsgBox lastCell.Row 'Fila final de datos
End Sub

Para encontrar la fila más inferior, se usa la propiedad SpecialCells(xlCellTypeLastCell) descrita en la sección ENCONTRAR LA ÚLTIMA FILA CON LA PROPIEDAD «UsedRange».

Probémosla para el caso de filas ocultas:

Y ahora para el caso de filas filtradas:

Y como vemos trabaja bien para ambos casos.

Funcionará bien, incluso con diversos bloques de filas ocultas con o sin datos.

La eficiencia de este método será muy buena mientras no haya muchas filas ocultas sin datos o estas no estén muy alejadas de la última celda con datos.

Es una pena que el lenguaje VBA de Excel y el modelo de objetos, no brinde medios más sencillos para realizar esta tarea, ya que es común trabajar con hojas de datos, con filtros y filas ocultas.