por Cuando empezamos a trabajar con Excel, una de las situaciones más comunes con la que nos encontramos es la de insertar filas en blanco en nuestros archivos. Esto no es un problema cuando contamos con poca cantidad de datos, pero la realidad suele complicar las cosas y nos podemos encontrar con que esos datos pueden ser miles de filas, y sería realmente lento (además de aburrido) el tener que insertar 1 o varias filas en blanco por cada fila con datos (por ejemplo) en un archivo de 4.000 filas.
Ante esta problemática, ¿cómo resolvemos el problema?. Vamos a poner un ejemplo que nos ayude a dar mayor claridad al post. Imaginad que tenéis una base de datos en la que tenéis los datos de facturación por horas y días de una empresa, algo así:
Con estos información, ahora tenemos que intercalar una fila en blanco por cada una de las filas con datos. Para realizar el trabajo de forma automática vamos a utilizar la siguiente macro:
Sub Insertar_filas()
'Definimos variables
Dim tTop, rFila, i, j As Double
'Buscamos el número de columna en el que estamos
nColum = ActiveCell.Column
'Buscamos la letra de la columna en la que estamos
lColum = Split(ActiveCell.Address, "$")(1)
'Determinamos el rango para definir la longitud del bucle for-next
Rango = lColum & ":" & lColum
'Indicamos el número de línea sobre el que comenzamos a añadir filas en blanco
tTop = 3
'Indicamos el número de filas en blanco a incluir
rFila = 1
fin = Application.CountA(ActiveSheet.Range(Rango))
For j = 2 To fin
'Si la primera celda (en este caso la 3) contiene un valor, entonces comenzamos a insertar filas
'intercaladas
If Not IsEmpty(Cells(tTop, nColum)) Then
For i = 1 To rFila
Cells(tTop, nColum).EntireRow.Insert
Next i
tTop = tTop + rFila + 1
End If
Next j
End Sub
Esta macro está diseñada para que las filas en blanco se agreguen en la en la columna sobre la que se encuentra la celda activa. Es decir, si ponemos el cursor sobre la celda A1 y se ejecutará, si lo hacemos en una columna sin datos no agregará filas.
Es interesante fijarse en algunas líneas de código, por ejemplo, para determinar la letra de la columna en la que estamos, debemos usar:
lColum = Split(ActiveCell.Address, "$")(1)
O para determinar el número de columna en el que estamos, debemos usar:
nColum = ActiveCell.Column
Una vez aplicada la macro, la información quedaría así:
Como podéis observar, ya tenemos una fila intercalada entre cada línea de datos. Si quisiésemos más líneas, por ejemplo 4 o 20, tan solo debemos indicar el número de columnas en rFila = (numero de lineas a insertar)
Para acabar, en el ejemplo que os dejo, he utilizado, al igual que en la entrada anterior, otra macro para activar esta macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnKey "{ESCAPE}", "Insertar_filas"
End Sub
La macro se activa pulsando la tecla «Escape». Pero podéis ejecutarla perfectamente vinculándola a un botón o ejecutándola directamente sobre el código.
Espero que este post os sea de utilidad, por experiencia, creo que es uno de los códigos más demandados y usados por los usuarios de Excel.
Descarga el archivo de ejemplo pulsando en: INSERTAR FILAS INTERCALADAS EN BLANCO EN EXCEL
buenísimo! y si quiero que lo haga a nivel celda en una sola columna? =)
Hola Cielito:
Es muy sencillo, solo has de modificar esta parte de la macro:
Cells(tTop, nColum).EntireRow.InsertPor esta otra:
Cells(tTop, nColum).Insert Shift:=xlDownEspero que te sirva.
Saludos.!
Hola
queria consultar en caso requiera columnas en vez de filas para el mismo proceso como cambio la macro, muchas gracias
Hola Ricardo: así lo puedes hacer:
Sub Insertar_columnas()'Definimos variables
Dim tTop As Long, rFila As Long, i As Long, j As Long
Dim fin As Integer
'incluimos columna en blanco a partir de la primera columna
tTop = 2
'Indicamos el número de columnas en blanco a incluir
rFila = 1
fin = Application.CountA(ActiveSheet.Range("1:1"))
For j = 2 To fin
If Not IsEmpty(Cells(1, tTop)) Then
For i = 1 To rFila
Cells(1, tTop).EntireColumn.Insert
Next i
tTop = tTop + rFila + 1
End If
Next j
End Sub
saludos.
Muchas gracias por tu ayuda fue de gran ayuda.-
saludos.-
Muy interesante Segu! Una cosulta, cómo lo podría hacer para agregar x filas pero cada cinco filas? Es decir, cinco filas con valores, añadir dos filas en blanco, cinco filas más con valores, añadir otras dos filas en blanco, etc.
Muchas gracias de antemano!
Hola David:
Solo tienes que modificar tres parámetros en la macro. La fila en la que empiezas, en este caso es la 7 (tTop) a partir de donde empezarán a insertase las dos filas en blanco rFila. Y finalmente indicar en el contador un +5
Pruebala en el ejemplo del post:
Sub Insertar_columnas()'Definimos variables
Dim tTop As Long, rFila As Long, i As Long, j As Long
Dim fin As Integer
'incluimos columna en blanco a partir de la primera columna
tTop = 7
'Indicamos el número de columnas en blanco a incluir
rFila = 2
fin = Application.CountA(ActiveSheet.Range("1:1"))
For j = 2 To fin
If Not IsEmpty(Cells(1, tTop)) Then
For i = 1 To rFila
Cells(1, tTop).EntireColumn.Insert
Next i
tTop = tTop + rFila + 5
End If
Next j
End Sub
Saludos.
Muchísimas gracias Segu, eres un auténtico crack!! Ha sido de gran utilidad!
Cuídate, un saludo.
…me vas a permtir que abuse de tus conocimientos y te haga una última pregunta… 🙂
Por casualidad no existirá alguna opción para que estas dos filas que me añade la macro, las añada sin bordes no?? Es decir, que fueran más bien dos filas en blanco sin que siguiera el recuadro de las anteriores…
Gracias de nuevo por tu gran aportación!
Hola David:
Tienes que añadir este fragmento de código en la macro, la parte que está en negrita:
For i = 1 To rFilaCells(tTop, nColum).EntireRow.Insert
Cells(tTop, nColum).EntireRow.Select
With Selection
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Next i
tTop = tTop + rFila + 5
Saludos
De acuerdo, te informo que acabas de adquir la categoría de semi diós!! Implacable. Muchas, muchas gracias!
Saludos!
David
Jaja! Me alegro que te haya servido. Saludos
Buenas de nuevo, Segu. Estoy atascado y he pensado que tal vez me podrías ayudar..
Necesito indicar dentro de una macro que me copie el texto desde la celda A2 hasta la última celda con datos (dado que es un listado que va cambiando el número de registros a diario).
No encuentro la forma de hacerlo. Te sabría mal echarme una mano?
Mil gracias de nuevo!!
No sé exactamente como es la estructura de datos que tienes. Pero un macro muy sencilla para hacer eso:
Sub Ejemplo_4()Dim Origen, Destino As Worksheet
Dim fin As Integer
Set Origen = Sheets("Hoja1")
Set Destino = Sheets("Hoja2")
fin = Sheets(1).Range("A2", Sheets("Hoja1").Range("A" & Sheets("Hoja1").Rows.Count).End(xlUp)).Rows.Count
With Destino
.Range("A1:C" & fin).Value = Origen.Range("A1:C" & fin).Value
End With
End Sub
Donde copiará en la hoja 1 el rango de datos de la celda A2 hasta la última celda con datos. y los igualará en el mismo rango de la hoja 2.
Echa un vistazo a este post, seguro que te ayuda:
https://excelsignum.com/2016/06/08/procedimientos-en-vba-para-mover-datos-entre-hojas/
Gracias una vez más. La cuestión es super básica, pasa que voy aun muy pez.
Esta es la parte macro, así de simple.
Sub Preparar_furgos()
Range(«A2:E50»).Select
Selection.Copy
Sheets(«FURGOS»).Select
Range(«A4»).Select
ActiveSheet.Paste
Range(«A1»).Select
End Sub
Lo que necesitaria es en lugar de asignarle el valor «E50» indicarle que seleccione hasta a la última celda activa, dado que ese registro es el que varía en función del día.
No sé si soy capaz de explicarme… 🙁
Gracias.
Hola David:
En la macro que te envíe tienes la respuesta. Veamos, para que excel comprenda que tiene que copiar un rango variable, necesitas crear una variable que cuente el número de registros y pasar la información al rango, de forma que automáticamente, cuando ese rango aumente excel sepa cuando registros tiene que seleccionar. Esto lo puedes hacer de varias formas, una de ellas es así:
Fin = Range("A2", Range("A" & Rows.Count).End(xlUp)).Rows.CountCon la variable «Fin» (o el nombre que quieres darle, contamos desde A2 hasta la última celda con datos. LO único que tenemos que hacer ahora es «decírselo» al rango en el que ahora tienes el 50, quedando así:
Range("A2:E" & Fin).SelectDe esta forma, ya tenemos el rango automatizado.
Tu macro, ahora estaría automatizada:
Sub Preparar_furgos()Fin = Range("A2", Range("A" & Rows.Count).End(xlUp)).Rows.Count
Range("A2:E" & Fin).Select
Selection.Copy
Sheets("FURGOS").Select
Range("A4").Select
ActiveSheet.Paste
Range("A1").Select
End Sub
Saludos.
Muchísimas gracias, una vez más!
Lo único es que aplicando esto tal cual, curiosamente me seleccionaba hasta la penúltima y me dejaba la úlitma fila de lado.
Finalmente he hecho una pequeña modificación y he cambiado esta línia «Fin = Range(«A2», Range(«A» & Rows.Count).End(xlUp)).Rows.Count» por esta otra: «Fin = Cells(Rows.Count, 1).End(xlUp).Row».
De verdad Segu, muchas gracias por todo. Me has ayudado mucho, prometo no molestarte más.
Saludos!!
Hola David:
Puedes preguntarme lo que necesites, no es molestia. Escribo este blog precisamente para ayudar.
Por cierto, la fórmula, pensé que tenías encabezados y no los queráis copiar, solo tienes que cambia de A2 por A1. Pero si con tu modificación te funcione, perfecto!.
Escribes este blog para ayudar y sin duda alguna logras tu propósito. Te tomo la palabra, si me veo en apuros te mandaré un S.O.S 😉
Thank you so much!
Muy buenas Segu,
Tengo una duda respecto botones de macro y tras buscar y buscar en foros no he podido dar con la solución.. te la planteo y si me puedes echar una mano perfecto.
La cuestión es que tengo un libro de excel que trabaja con distintas macros que he creado, las cuales se activan a través de cuatro botones creados a tal efecto. Lo que necesitaria, si fuera posible, es lo siguiente:
1.- Una macro que impidiera que se ejecute una macro antes que otra. Dicho de otro modo, que si el usuario le da a un botón antes que al otro (de deben ir ejectuando en orden consecutivo del 1º al 4º), la macro no se active y salte un mensaje de error o un cuadro de texto concreto.
2.- Una macro que impida modificar el formato de los botones o que se puedan eliminar por error. Esto lo he conseguido mediante la protección de la hoja, habilitando todo menos los botones. Hasta ahí perfecto dado que no deja seleccionar el botón, sinó únicamente ejecutar la macro que tiene asignada. El problema viene porque en la hoja de trabajo se deben copiar y pegar datos, y dado que la hoja está protegida no permite esta opción, con lo cual la protección no me resulta viable.
No sé hasta que punto resulta esto posible pero no he encontrado nada en ningún sitio…
Muchas gracias de antemano!
David.
Hola David:
Sobre la primera pregunta, lo que me se ocurre es que juegues con la ejecución de las macros y que utilicen una hoja oculta. De forma que si ejecutas la primera macro, en la celda A de ese hoja se marque con un numero, si ejecutas la dos, es necesario que esa celda tenga ese número si no, lanzas mensaje, si lo está ejectas la segunda macro y marcas otra celda con otro número y así con todos los casos. Es decir debes jugar con un condicional y los elementos de la hoja.
Sobre la segunda, pues claro que puedes ejecutar una macro con hojas protegidas: esta macro es de las primeras del blog:
https://excelsignum.com/2013/07/21/ejecutar-macros-en-libros-protegidos-con-contrasena/
Puedes hacer ambas cosas, pero la primera requiere un poco más de trabajo.
Espero haberte contestado u orientado.
Saludos.
Gracias de nuevo por tu respuesta y más aun por tu rapidez!
La segunda parte solucionada, perfecto.
Respecto la primera, me parece una opción muy astuta, pero me podrías indicar cómo seria más o menos el código para hacerme una idea..?
Mil gracias.
Perfecto Segu, ya lo tengo! Siguiendo tu consejo y con el condicional me ha ido genial!
Mil gracias de nuevo por tu infinito conocimiento!
Te debo una…bueno..te debo ya unas cuantas!
Saludos!
Muy bien David, me alegro que lo hayas solucionado. Saludos.
Muy buenas de nuevo! Con tu permiso, te lanzo una última consulta con la que ya finalizaría mi trabajo…(por fin!). A ver si me puedes ayudar:
Necesitaría indicar dentro de la macro que el valor de la celda «F1» se autocomplete en las celdas inferiores hasta la última celda activa de la columna de su izquierda. Es esto posible?
A ver si me logro explicar:
En el siguiente fragmento de escritura de la macro logro que la pabra «SURT» se me autorellene hasta la última celda con texto de la misma columna,
Range(«F2»).Select
ActiveCell.FormulaR1C1 = «SURT»
Selection.Copy
Fin = Cells(Rows.Count, 6).End(xlUp).Row
Selection.AutoFill Destination:=Range(«F2:F» & Fin)
Range(«F2:F» & Fin).Select
Si embargo, en este otro fragmento, necesitaria que el resultado de la fórmula aplicada escrito en la celda «F2» de otra hoja, se autorellenara hacia abajo pero hasta la última celda con texto de la columna «E» que tiene a su izquierda.
ActiveCell.FormulaR1C1 = «=IFERROR(VLOOKUP(RC[-1],’DADES URIA’!C[-1]:C,2,0),0)»
Range(«F2»).Select
Selection.AutoFill Destination:=Range(«F2:F» & Fin)
Range(«F2:F» & Fin).Select
En resumen, no sé indicar que me haga el autofill de la F2 hacia abajo hasta la última celda activa de la columna E.
Muchas gracias!
Hola David:
Prueba con un loop (for next) es muy más sencillo:
Sub rellena()Dim i As Integer
Dim fin As Integer
With ActiveSheet
fin = Application.CountA(.Range("a:a"))
For i = 2 To fin
If .Cells(i, 2) = Empty Then
.Cells(i, 2) = .Cells(i, 1)
Else
Exit For
End If
Next i
End With
End Sub
En este ejemplo en la columna A (con encabezado), rellenará la columna B hasta la celda que tenga datos, luego dejará de copiar.
Pruébala.
Saludos.
Yo no entiendo de programación pero he intentado hacer una macro con los datos expuestos aquí y no funciona, quizás no se ejecute.
Yo quiero insertar una fila en blanco, desde la primera fila cada 100 filas hasta el final; independientemente de los datos incluidos.
¿Me puede realizar la macro?. Gracias anticipadas.
Hola Ignacio.
La respuesta se la dí a la consulta que plantea David (mira el historial de consultas de este post.
Sub Insertar_columnas()
‘Definimos variables
Dim tTop As Long, rFila As Long, i As Long, j As Long
Dim fin As Integer
tTop = 100
rFila = 1
fin = Application.CountA(ActiveSheet.Range(«1:1»))
For j = 2 To fin
If Not IsEmpty(Cells(1, tTop)) Then
For i = 1 To rFila
Cells(1, tTop).EntireColumn.Insert
Next i
tTop = tTop + rFila + 100
End If
Next j
End Sub
ASí debería funcionarte sin problema. Puedes cambiar los parámetros según los datos que tengas. Saludos.