Encontrar la última fila con datos con VBA

Transcripción del vídeo

Las macros que crearemos en esta ocasión son muy simples pero efectivas para encontrar la última fila con datos.

Revisaremos tres métodos y analizaremos las ventajas de cada uno de ellos.

Trabajaremos con tres ejemplos de datos y que puedes ver a continuación.

En el primer caso, los datos están a partir de la primera fila de la hoja y la última fila es la siete.

En el segundo ejemplo, he desplazado hacia abajo el rango de datos y por lo tanto la última fila en uso será la fila diez.

En el último ejemplo, he insertado algunas filas en blanco entre los datos, pero aun así quiero que la macro me devuelva la fila 13 que es la última fila en uso.

Para nuestra primera macro utilizaré la propiedad UsedRange y antes de ingresar el código explicaré un poco al respecto.

La propiedad UsedRange siempre nos indica el rango utilizado en una hoja, es decir el rango de celdas que contiene algún tipo de dato.

Por ejemplo, los datos que se ven en pantalla ocupan el rango A1:C3 y, por lo tanto la propiedad UsedRange nos devolverá dicho rango.

En este segundo ejemplo, la propiedad UsedRange nos devolverá el rango A1:D7.

Esta propiedad la podemos combinar con el método SpecialCells el cual nos devolverá aquellas celdas que cumplen con un determinado criterio y que para nuestro ejemplo será el criterio de la última celda determinado por la constante xlCellTypeLastCell.

Pero vayamos a Excel para crear nuestra primera macro. Iré a la pestaña Desarrollador y pulsaré el botón Visual Basic.

En seguida insertaré un nuevo módulo de código desde el menú Insertar > Módulo e insertaré la subrutina UltimaFila_1

Nuestra macro consistirá en una sola línea que es la siguiente. Utilizaré la instrucción MsgBox para mostrar un cuadro de diálogo con el número de fila.

En seguida, ActiveSheet para referirnos a la hoja activa. Posteriormente UsedRange para obtener el rango de celdas utilizado.

SpecialCells para indicar que necesitaremos una celda específica de dicho rango y la constante xlCellTypeLastCell para indicar que queremos la última celda del rango en uso.

Terminemos esta línea de código con la propiedad Row para pedir a Excel que nos devuelva la fila de esta última celda.

Y eso es todo para esta macro y para probarla, regresaré a Excel y en la Hoja1 iré a la pestaña Desarrollador y pulsaré el botón Macros.

Elegiré la macro UltimaFila_1 y pulsaré el botón Ejecutar y obtendré como resultado la fila siete que es el resultado correcto.

Iré a la Hoja2 y volveré a ejecutar la macro desde la pestaña Desarrollador > Macros y pulsaré el botón Ejecutar y en esta ocasión obtendré la fila diez porque esa es precisamente la última fila en uso.

Haré lo mismo con la Hoja3 yendo al botón Macros y ejecutando nuestra macro para obtener como resultado la fila trece.

Con esto comprobamos que nuestro código funciona correctamente al devolver la última fila en uso.

La propiedad UsedRange funcionará perfectamente hasta que tengas un caso como el siguiente.

Supongamos que, por alguna razón, necesitas colocar una celda con algún valor que se encuentra por debajo de los datos.

Por ejemplo, en la celda F9 colocaré el texto Año y en la celda G9 el valor 2017 ¿cuál crees que será el resultado de la macro?

Con estos nuevos valores en la fila nueve, el rango utilizado de nuestra hoja está más abajo que el rango donde se encuentran los datos.

Iré de nuevo a la pestaña Desarrollador > Macros y al pulsar el botón Ejecutar obtendré la fila nueve que es precisamente la fila donde ingresé el dato del año.

Este es el único inconveniente de utilizar la propiedad UsedRange, cuando tenemos algún valor debajo de los datos obtendremos esa última fila.

Si te encuentras en un caso como este, entonces será conveniente evaluar el uso de otro método como el que veremos a continuación.

Nuestra segunda macro utilizará la propiedad End para encontrar la última celda con datos.

Primero te mostraré los principios que se encuentran detrás de esta macro.

El primer paso será encontrar la manera de ir a la última celda de la columna y en caso de estar en Excel, lo podemos lograr pulsando la combinación de teclas Ctrl+Flecha abajo.

Observa que de inmediato llego a la fila 1048576 y si pulso la combinación de teclas Fin+Flecha arriba, Excel me llevará de inmediato a la última celda con datos.

Si logramos replicar este comportamiento en nuestra macro, podemos encontrar fácilmente la última fila.

Regresaré al Editor de visual Basic y dentro del módulo de código ingresaré la nueva subrutina que llamaré UltimaFila_2.

De igual manera utilizaré la función MsgBox y en seguida ingresaré el objeto ActiveSheet.Cells y como primer parámetro colocaré la instrucción Rows.Count

Esta instrucción me devolverá el total de filas de la hoja y como segundo parámetro indicaré la columna A.

Esta instrucción es la encargada de hacer referencia a la última celda de la columna A, así que enseguida utilizaré la propiedad End con el parámetro xlUp para pedir a Excel que vaya hacia arriba y se detenga en la primera celda con contenido.

Esto es equivalente a pulsar el atajo de teclado Fin+Flecha arriba y por lo tanto llegaremos a la última fila con datos.

Al final de esta línea de código ingresaré la propiedad Row para obtener la fila de la última celda con datos.

Cambiaré de ventana para probar la macro en Excel. Iré a la Hoja2 y desde la ficha Desarrollador > Macros, elegiré la segunda macro y pulsaré el botón Ejecutar.

Como resultado obtenemos la fila diez que es precisamente la última fila con datos.

Puedo colocar datos en otras celdas como el año 2017 en las celdas F12 y G12, pero la macro siempre tomará como referencia la columna A para decirme cuál es la última fila con datos.

Observa que al ejecutar de nuevo la macro vuelvo a obtener la fila 10.

Lo único que debes hacer para que esta macro devuelva el resultado esperado es elegir adecuadamente la columna que nos servirá para indicar la siguiente fila vacía en los datos.

Estos dos métodos que hemos revisado son los más conocidos, pero te mostraré una tercera alternativa la cual utiliza el método Find para encontrar la última celda con contenido.

Regresaré al Editor de Visual Basic y voy a copiar y pegar el código de la macro para poder explicarte su funcionamiento.

La primera línea me ayudará a evitar un error cuando el método Find no encuentre una celda con contenido lo cual sucederá cuando la columna está vacía.

En seguida utilizo la función MsgBox para mostrar el número de fila y la primera instrucción es ActiveSheet.Columns e indico la columna A que me ayuda a hacer referencia a la columna A que es donde quiero realizar la búsqueda.

En seguida está el método Find y su primer parámetro es un asterisco para indicar que queremos encontrar cualquier valor.

El parámetro SearchOrder le pedirá una búsqueda por filas y el parámetro SearchDirection pedirá una búsqueda del valor previo como si fuera una búsqueda hacia atrás en las filas de la columna.

Finalmente utilizo la propiedad Row para obtener el número de fila.

Regresaré a Excel y en la Hoja3 iré a la pestaña Desarrollador > Macros y ejecutaré la tercera macro que me devolverá como resultado la fila trece que es la última fila de los datos.

Te recomiendo descargar el archivo de trabajo y probar cada una de las macros sobre los datos de ejemplo o con tus propios datos.

Para finalizar he creado un ejemplo muy sencillo que utiliza un botón ActiveX para insertar la hora al final de la columna.

Observa el código del evento Click del botón. Comienzo por definir una variable de tipo Long que almacenará el número de la última fila con datos.

En seguida, utilizo el código de la propiedad End para obtener la última fila de la columna A.

Recuerda que este código es el que implementamos en la segunda macro.

Una vez que tengo el número de fila de la última celda con datos, entonces utilizo la propiedad Cells sumando uno al valor de la variable ultima para llegar a la siguiente fila disponible y como segundo argumento el valor uno que equivale a la columna A.

Al valor de esta celda le asigno el resultado de la función Time.

Regresaré a la hoja de Excel y pulsaré el botón para insertar el valor de la hora al final de la columna A.

Sin importar las veces que pulses este botón, la nueva hora se insertará siempre en la siguiente fila disponible.

Revisa cada una de las macros que hemos creado en esta ocasión y pruébalas con tus datos para implementar la que mejor se acomode a tus necesidades o a tu estilo de programación.