Lección 11: Buscar el inicio y fin de datos de una tabla con macros.

Escrito por Administrador

23 de agosto de 2021

En esta lección, vas a aprender a buscar la fila o columna donde se inicia o termina el contenido de una tabla, algo esencial para conocer el rango de datos y poder automatizar procesos.

La sintaxis del comando que usaremos para filas es la siguiente:

h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Para columnas:

h = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 0).Column

Analicemos el primero de ellos.

¿Qué hace este comando? Asigna a la variable h el número de la última fila que tiene la primera columna rellena.

¿Cómo funciona? Vamos a verlo. En la primera parte del comando,

h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

señalamos a una celda. ¿Cuál? Rows.Count se refiere a la última fila de una hoja Excel, la 65536. Así, que estamos apuntando a la celda (65536,1) es decir, a la fila 65536, columna A.

Si quisiéramos buscar desde la fila 280, solo tendríamos que escribir:

h = Cells(280, 1).End(xlUp).Offset(0, 0).Row

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. Esta es la celda que estamos buscando, la última fila con datos de la columna A.

h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Si quisiéramos buscar hacia abajo escribiríamos: xlDown.

Para columnas; xlToLeft o xlToRight.

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

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

Todo lo visto es extensible al comando para trabajar con columnas

h = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 0).Column

Vamos a entender todo esto mejor con unos cuantos ejemplos.

Crea un libro nuevo, abre el editor VBA, inserta 7 módulos y copia las siguientes macros en ellos.

Macro 1

Sub Leccion11_1()

'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

Sub Leccion11_2()

'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

Sub Leccion11_3()

'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

Sub Leccion11_4()

'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

Sub Leccion11_5()

'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

Sub Leccion11_6()

'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

Sub Leccion11_7()

'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

Crea ahora siete botones y asociales las macros anteriores. Rellena la hoja como aparece en la figura.

total-operaciones-matematicas-macros

Ejecuta la primera macro para encontrar la primera fila de la columna G que contiene datos. 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.

total-operaciones-matematicas-macros

Seguimos ejecutando macros hasta la sexta incluida, resultando una figura como la siguiente:

total-operaciones-matematicas-macros

La séptima macro 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. Espero que te esté gustando el curso y que me plantees cualquier duda que te surja. También puedes proponerme macros que te interese desarrollar y las voy incluyendo en el temario, soy todo oídos. Nos vemos en el próximo capítulo.

Descarga el archivo del ejemplo

« L10. Filtrar y ordenar datos con macros.

L12. Condicionales en macros (1). »

0 comentarios

Enviar un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Quizá te interese,

Contacto