martes, 13 de septiembre de 2011

VBA: Cómo insertar filas. El método Insert.

Aprenderemos hoy a emplear la instrucción de VBA que nos permite insertar filas en nuestra hoja de cálculo.
La entrada da solución al problema planteado por un usuario, como continuación de un post anterior (ver):

...como hago si no tengo las filas creadas en blanco, ¿hay alguna función que cree esas filas?.
[...]en este caso yo quiero que se me auto complete con el codigo 000025, 000026, 000027 y asi con los restantes codigos...


La idea es que nuestra macro verifique, en primer lugar, donde hay un salto de valores en el rango de trabajo; una vez haya encontrado dicho salto insertaremos tantas filas como se requieran para completar la continuidad; por último completaremos las nuevas filas con los valores correspondientes.
Veamos nuestro listado de valores, fijándonos en los saltos de valor marcados en amarillo:


Por ejemplo, la macro evaluará la fila 5 y 6, donde ha encontrado un salto entre los valores de las celdas (pasa de 3 a 10); lo que implicará que nuestra macro insertará 6 filas nuevas, y cada nueva fila la completará con el valor adecuado, esto es, valores de 4 a 9...
Veamos nuestro código VBA, que se reparte entre dos macros (CompletarFilas() y ultimafila().
Nuestra programación se incluirá el código en un módulo del Editor de VBA (Alt+F11) será:

'declaramos variables públicas que sirvan para
'todo los procesos de este proyecto.
Public UltFila As Long
Public rng As Variant

Sub CompletarFilas()
'www.excelforo.blogspot.com
Dim hoja As Worksheet
Dim celda As Object
Dim destino As Integer
Dim fila1 As Integer, fila2 As Integer

ScreenUpdating = False
Set hoja = Sheets("Hoja1")
'llamamos la macro 'ultimafila para que calcule
'de manera actualizada cuál es dicha fila
ultimafila

For Each celda In hoja.Range(rng)
'con destino calculamos el salto de valores
destino = celda.Offset(1, 0).Value - celda.Value - 1
fila1 = Range(celda.Address).Row + 1
fila2 = Range(celda.Address).Row + destino
'comprobamos si hay salto de valores en el rango
If celda.Offset(1, 0).Value - celda.Value > 1 Then
'si existe, insertamos tantas filas como valores falten por completar
Rows(fila1 & ":" & fila2).Insert Shift:=xlDown
'rellenamos dichas celdas con los valores correspondientes
For i = fila1 To fila2
Cells(i, 1).Value = i - 2
Next
End If
If celda.Offset(1, 0).Row > UltFila Then Exit Sub Else ultimafila

Next celda

ScreenUpdating = True
'liberamos las variables de objeto
Set hoja = Nothing
Set rng = Nothing

End Sub

Sub ultimafila()
Dim hoja As Worksheet

'Encontramos la última fila con valores CADA VEZ
Set hoja = Sheets("Hoja1")
UltFila = hoja.Range("A" & hoja.Rows.Count).End(xlUp).Row
rng = hoja.Range("A2:A" & UltFila).Address

End Sub


Si ejecutamos la macro CompletarFilas comprobaremos cómo responde según lo esperado, esto es, insertando filas y dándoles los valores correctos.

21 comentarios:

  1. Hice la prueba y resulta, aunque tengo una interrugante:
    Mis datos numericos TIENEN OBLIGATORIAMENTE que comenzar por 0(cero)o 0000(segun necesite) y OBLIGATORIAMENTE en A2.
    Si tengo el inicio de datos numericos comenzando en A5 o A11, no funciona perfecto.
    Otra es la colocacion de cantidad de caracteres numericos, si tengo 0000 al completar las filas coloca un solo caracter.
    ¿podria hacerse de forma que responda a la necesidad segun tenga la catidad de caracteres en celda y comenzar donde encuentre el primer numerico en columna A?

    ResponderEliminar
  2. Lo de la columna A, no es problema porque puede cambiarse facil pero dinamicamente en las filas si `puede ser necesario que sea en la 3, 12, 9, o 23

    ResponderEliminar
  3. Hola Joao,
    es cierto que la macro del post es muy específica, pero se podría adaptar a lo que necesitas.
    Por ejemplo, el tema del formato del número introducido, sería suficiente asignale un formato de número personalizado tipo 0000.
    Si los valores comienzan en una fila diferente a la 2, así a bote pronto, te diría que modificaras la línea 30 de la macro:
    Cells(i, 1).Value = i - 2
    variando el 2 por el número de fila donde comenzasen tus datos, bien con alguna variable o bien a mano... creo que funcionaría bien.
    Espero te oriente en lo que necesitas.
    Un saludo

    ResponderEliminar
  4. variando el 2 por el número de fila donde comenzasen tus datos, bien con alguna variable o bien a mano... creo que funcionaría bien.
    el 2 lo vario y funciona pero si fuera una variable no tendria que estar cambiando el 2 siempre que necesite, ¿no te parece?
    Lo que pasa es que mi experiencia es casi nula para crear una variable y como se crea.
    ¿Si pudieras darme una mano en eso?, agradecido

    ResponderEliminar
  5. Hola Joao,
    no conozco tu planteamiento exacto, pero una manera de asociarlo a una variable sería mediante un INPUTBOX.
    Tendrías que añadir el siguiente código:
    Dim variable As Integer
    variable = InputBox("¿en que fila empiezan los valores??")

    y reemplazar la fila 30 por:
    Cells(i, 1).Value = i - variable

    Hay más formas, pero esta es de las más sencillas... simplemente al ejecutar la macro te aparecerá una ventanita que pregunta el número de fila, sólo teclea el número y listo...

    Espero te sirva.
    Slds

    ResponderEliminar
  6. Perfecto.
    ¿Como haria yo para enviarte un ejemplo de lo que quiero?.
    Es que tengo en columna A con formato Personalisado a Clie0000 y el codigo me dá error en la linea 21.
    otra forma seria yo usar solo formato 0000 en ves de Clie0000. Comienzan los codigos(datos en A2 0000(A1 Titulo) y si le digo en el ImputBox que complete a partir de la A6, este me complementa de A3 a A5 con -0001, -0002, -0003 y A6 0000

    ResponderEliminar
  7. Hola de nuevo Joao,
    envíame lo que tengas a
    excelforo@gmail.com

    Es cierto que la macro del ejemplo es muy especifica del problema que planteaba concreto que se plantea...
    en ocasiones la didactica está reñida con lo práctico, y no se puede dejar solucionado totalmente algo.
    Espero tu correo.
    Slds

    ResponderEliminar
  8. Buenos dias, antes que nada felicitarlos por este blog. En mi trabajo utilizamos macros pero yo apenas estoy aprendiendo a programar y necesito una mano. Es posible utilizar insert para que inserte filas hasta completar un numero dado de filas?Por ejemplo datos anuales (12 filas) para una lista de valores en la que no siempre los 12. MUCHAS GRACIAS

    ResponderEliminar
    Respuestas
    1. Hola, agradezco tus palabras y me alegro te guste mi blog personal de ayuda.

      Respecto a tu cuestión, es un poco vaga, no me queda claro si al final siempre tienes que insertar 12 filas, o puede depender de alguna condición.
      Pero una respuesta general, es que sí es posible insertar las filas que quieras, sería suficiente tener claro en cada momento cuántas filas necesitas, y definir una variable con ese valor, para luego realizar esa inserción, basada en el resultado.
      Podrías para ello aplicar el código de esta entrada...

      Espero haberte orientado, si no es así, puedes enviarme un ejemplo a:
      excelforo@gmail.com
      Slds

      Eliminar
  9. Hola buenos dias, te acabo de mandar un correo a excelforo@gmail.com para ver si puedo contar con tu ayuda en un ejercicio que tengo de un curso que estoy llevando de macros...igual y yo tambien tengo cuenta en gmail.. podemos estar en constante dialogo si es que puedes ayudarme.

    De antemano gracias y excelente blog.

    Saludos!

    ResponderEliminar
  10. Estuve buscando como darle solucion a mi caso y no he podido encontrarlo... si pueden ayudenme no se si con alguna formula o en VBA:

    Datos concretos:

    tengo esta tabla:
    5 Nacional URU 2 3 Barcelona





    1 Boca Juniors 1 0 Toluca
    7 Toluca 4 3 Nacional URU
    0 Barcelona Boca Juniors
    0 Toluca Barcelona
    0 Boca Juniors Nacional URU
    0 Barcelona Toluca
    0 Nacional URU Boca Juniors
    0 Boca Juniors Barcelona
    0 Nacional URU Toluca
    0 Barcelona Nacional URU
    0 Toluca Boca Juniors

    Si yo coloco el resultado como el primer caso me dara como resultado 5 goles, y manualmente tendre que insertar 5 filas para colocar los nombres de los jugadores que metieron los goles.
    Y es tedioso insertar las filas a cada momento.
    ¿Hay alguna formula que inserte las filas a partir de un resultado, en este caso 5 e insertar 5 filas?


    Les agradezco la respuesta

    ResponderEliminar
    Respuestas
    1. Hola Roy,
      no, mediante fórmulas no es posible; se necesita una macro para esto.
      REvisa el código descrito en esta misma entrada, ya que lo que hay que aplicar es el método Insert.
      En esencia tienes que hacer lo mismo, recorrer el rango de la primera columna (donde aparecen el total de goles del partido), e ir insertando un número de filas igual a éste.

      Slds

      Eliminar
  11. Muy buenas noches estoy intentando una macro que evalúe condiciones entre dos celdas, ejemplo si b1 es menor que e1 aumentar en las columnas
    a y b de la fila comparada. Alguna idea por favor de antemano muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      entiendo que te refieres a incrementar el valor de las celdas a1 y b1 (según tu ejemplo). si es así, podrías emplear un conficional en tu macro
      IF ... THEN comparando B1 y E1 y luego incrementar el valor de A1 ó B1 según corresponda
      Puedes ver un ejemplo similar que te podrá dar una idea
      http://excelforo.blogspot.com.es/2011/01/vba-macro-para-acumular-cantidades.html

      Espero te oriente

      Eliminar
  12. Hola, Ismael.
    Antes que nada, quiero felicitarte por este gran post, y en especial, por tu gran foro.

    Tengo un caso particular que se adapta muy bien a tu código, únicamente con unas cuantas diferencias:

    Tengo una hoja con entradas: columna de cantidad, código y descripción. Puede haber varias entradas de un mismo código, y están ordenadas según el código.

    Lo que deseo: que crea una fila cada cambio de código, para insertar una sumatoria de cantidad y copiar el código (la columna de descripción queda vacía). Además, esta celda debe quedar con formato de negritas. ¿Me pudieras ayudar a adaptarlo? De antemano, ¡Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola Mike,
      has pensado que con una Tabla dinámica sobre ese rango tendrías lo que quieres sin macros??.. o incluso, si ya lo tienes ordenado, aplicar la herramienta Subtotales ?

      Construir una macro supondría aplicar primero un bucle (del tipo for..next) para ir comparando cada celda con la posterior.. cuando sea diferente(ya que los tienes ordenado) aplicar el método Insert...

      Saludos cordiales y gracias por tus palabras!

      Eliminar
    2. Hola Ismael:

      ¡Muchas gracias por la respuesta! No recordaba la herramienta Subtotales (hace mucho la utilicé en un curso, pero nada más aquella remota vez), y me ha funcionado de maravilla.

      Eliminar
  13. no soy muy experto pero me gustaría que me ayudaras si quiero que la macro me inserte un numero de filas a partir de un dato en una celda especifica como puedo hacerlo.

    Es decir tengo 1000 registros en la columna k aparece el numero de filas a insertar
    como puedo hacerlo si me puedes ayudar con el código seria mucho mejor

    ResponderEliminar
    Respuestas
    1. Hola Fer,
      prueba con
      Sub Macro1()
      Dim filas As String
      For fila = 5 To 2 Step -1
      filas = fila + 1 & ":" & fila + Cells(fila, "K").Value
      Rows(filas).Insert Shift:=xlDown
      Next fila
      End Sub

      saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.