En esta lección, vas a aprender a buscar la primera y última fila o columna con datos de una tabla usando una macro, algo ciertamente importante para conocer el rango de datos y poder automatizar procesos.
En primer lugar, la sintaxis del comando que usaremos para filas es la siguiente:
h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
Y, para columnas:
h = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 0).Column
Primero, vamos a analizar la sintaxis del comando para filas.
En realidad, este comando lo que hace es asignar a la variable h el número de la última fila que tiene la primera columna rellena.
Para ello, Rows.Count se refiere a la última fila de una hoja Excel, la 65536. De esta manera, estamos apuntando a la celda (65536,1), es decir, a la fila 65536, columna A.
h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
De hecho, si quisiéramos buscar desde la fila 280, solo tendríamos que escribir:
h = Cells(280, 1).End(xlUp).Offset(0, 0).Row
Mientras, la segunda parte del comando, comienza a mirar desde la celda a la que hemos apuntado antes, hacia arriba, y va recorriendo todas las filas de la columna A hasta que encuentra una celda con datos. De esta forma, hemos encontrado la celda que estábamos buscando, la primera fila con datos de la columna A.
h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
En caso contrario, si quisiéramos buscar la última fila de la columna A usando una macro, escribiríamos: xlDown.
Del mismo modo, para columnas; xlToLeft o xlToRight.
Por último, la tercera parte del comando, suma al resultado anterior el número de filas o columnas que le indiquemos. En este caso, no le sumaríamos ninguna, y el resultado de la variable h sería simplemente el número de la última fila que contiene datos de la columna A.
h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
Así mismo, si quisieras guardar en h el número de la primera fila libre después de la tabla, solo tendrías que sumar una fila al resultado de la búsqueda anterior, y escribirías:
h = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Finalmente, decirte que todo lo visto es extensible al comando para trabajar con columnas
h = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 0).Column
Seguramente vamos a entender todo esto mejor con unos cuantos ejemplos en los que veremos como buscar la primera y última fila o columna usando una macro.
En primer lugar, crea un libro nuevo, abre el editor VBA e inserta 7 módulos. Después, copia las siguientes macros en ellos.
Macro 1. Buscar la primera fila con datos.
'Macro que localiza la primera fila con datos de la columna G (columna 7)
'En la columna G buscamos desde la fila 1 hacia abajo la primera fila con datos que se encuentre
h = Cells(1, 7).End(xlDown).Offset(0, 0).Row
'Escribimos el número de esa fila en la celda N25
Cells(25, "N").Value = h
'Marcamos la celda encontrada con contorno rojo
Cells(h, "G").BorderAround _
ColorIndex:=3
End Sub
Macro 2. Buscar la última fila con datos.
'Macro que localiza la última fila con datos de la columna G (columna 7)
'En la columna G buscamos desde la fila 65536 hacia arriba la primera fila con datos que se encuentre
h = Cells(Rows.Count, 7).End(xlUp).Offset(0, 0).Row
'Escribimos el número de esa fila en la celda N27
Cells(27, "N").Value = h
'Marcamos la celda encontrada con contorno azul
Cells(h, "G").BorderAround _
ColorIndex:=5
End Sub
Macro 3. Buscar la última fila con datos.
'Macro que localiza la última fila con datos de la columna H (columna 8)
'En la columna H buscamos desde la fila 65536 hacia arriba la primera fila con datos que se encuentre
h = Cells(Rows.Count, 8).End(xlUp).Offset(0, 0).Row
'Escribimos el número de esa fila en la celda N29
Cells(29, "N").Value = h
'Marcamos la celda encontrada con contorno verde
Cells(h, "H").BorderAround _
ColorIndex:=10
End Sub
Macro 4. Buscar la primera columna con datos.
'Macro que localiza la primera columna con datos de la fila 12
'En la fila 12 buscamos desde la columna 1 hacia la derecha la primera columna con datos que se encuentre
h = Cells(12, 1).End(xlToRight).Column
'Escribimos el número de esa columna en la celda N31
Cells(31, "N").Value = h
'Marcamos la celda encontrada con contorno violeta
Cells(12, h).BorderAround _
ColorIndex:=7
End Sub
Macro 5. Buscar la última columna con datos.
'Macro que localiza la última columna con datos de la fila 9
'En la fila 9 buscamos desde la última columna de la hoja de cálculo hacia la izquierda la primera columna con datos que se encuentra
h = Cells(9, Columns.Count).End(xlToLeft).Column
'Escribimos el número de esa columna en la celda N33
Cells(33, "N").Value = h
'Marcamos la celda encontrada con contorno amarillo
Cells(9, h).BorderAround _
ColorIndex:=6
End Sub
Macro 6. Buscar primera fila sin datos.
'Macro que localiza la primera fila sin datos en la columna G después de la tabla
'En la columna G buscamos desde la fila 65536 hacia arriba la primera fila con datos que se encuentre, que será la última de la tabla, y le suma una fila
h = Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Row
'Escribimos el número de esa fila en la celda N35
Cells(35, "N").Value = h
'Marcamos la celda encontrada con contorno naranja
Cells(h, "G").BorderAround _
ColorIndex:=46
End Sub
Macro 7. Restablecer el formato de la tabla.
'Macro que restablece el formato de la tabla
'Borramos el contenido de las celdas del rango N25-N36
Range("N25:N36").ClearContents
'Sin bordes
Range("G8:H23").Borders.LineStyle = xlNone
'Estos tres comandos reestablecen la cuadrícula de la hoja excel
Range("G8:H23").Interior.Pattern = xlNone
Range("G8:H23").Interior.TintAndShade = 0
Range("G8:H23").Interior.PatternTintAndShade = 0
End Sub
Seguidamente, crea siete botones y asociales las macros anteriores. Después, rellena la hoja como aparece en la figura.
Probablemente, te interese este enlace donde explico cómo asignar macros a botones.
A continuación, ejecuta la primera macro para encontrar la primera fila de la columna G que contiene datos. Como resultado, el número de la fila aparecerá en la casilla marcada de rojo y además, la celda aparecerá con un borde alrededor del mismo color.
De igual forma, seguimos ejecutando macros hasta la sexta incluida, resultando una figura como la siguiente:
Además, se ha incluido la séptima macro que restablecerá la hoja de cálculo para volver a ejecutar las macros si se desea.
Con esto llegamos al final de la undécima lección, en la que has aprendido a buscar la primero y última fila o columna con datos de una tabla usando una macro.
Por último, quiero invitarte a compartir tus dudas en los comentarios o en el foro. Intentaré ayudarte en todo lo que pueda, y así aprenderemos todos.
Nos vemos en el próximo capítulo.
Si te ha servido y quieres donar
Descarga el archivo del ejemplo
4 Comentarios
Enviar un comentario
« L10. Filtrar y ordenar datos con macros.
L12. Condicionales en macros (1). »
Buenas, tengo una macro que realiza tareas a una base de datos que descargo de diferentes programas, por lo tanto, cada programa tiene un numero de columnas diferente, y necesito que la macro realice una operación en la una columna después de la ultima columna activa, ese no seria el problema ya que con estas lecciones logre mi objetivo, en parte, sin embargo, en esa columna toca insertar una formula que hace referencia a la primera fila, en este caso no he logrado validar correctamente esa formula ya que al hallar la primera fila no me deja reemplazarla en la formula
Range(Cells(2, ult), Cells(2, ult - 1).End(xlDown).Offset(0, 1)) = _
"=+IF(VALUE(LEFT(R[1]C[-8],RC[-1]))=RC[-8],"""",""Auxiliar"")"
quiero reemplazar el -8 por el valor encontrado para la primera fila con macro
ult = Range("XFD1").End(xlToLeft).Column
h = -(Cells(ult, 1).End(xlToRight).Column)
Range(Cells(2, ult), Cells(2, ult - 1).End(xlDown).Offset(0, 1)) = _
"=+IF(VALUE(LEFT(R[1]C[h],RC[-1]))=RC[h],"""",""Auxiliar"")"
'Escribimos el número de esa columna en la celda N31
Buenos días Hernán,
Ya que estás usando VBA, lo ideal es que las fórmulas las referencies a variables. Como el formato de la página es dinámico, debería crear una variable que amortigüe ese cambio de columnas para que la fórmula siga funcionando.
Si te parece, abre un hilo en el foro y sube un ejemplo para poder ver en más detalle el problema.
Saludos.
muchas gracias, efectivamente definiendo-lo como variable, así logre que la linea de la macro superara el himpase gracias a la explicación del profe ya que soy neófito en este campo no lograba ingresar la variable a la formula cuando empezaba con la columna, ahora me toca verificar nuevamente la definición de la variable ya que se adicionaran mas columnas al final de la base de datos durante todo el recorrido de la macro.
Muy bien Hernan.
Para cualquier cosa, por aquí ando.
Saludos.