jueves, 8 de septiembre de 2011

Rellenar celdas en blanco en Excel.

Para responder a la cuestión planteada por un lector del blog, emplearemos una herramienta poco conocida, Ir a especial > celdas en blanco:

...tengo una lista de más de 5000 productos y hemos realizado un inventario, resulta que al pasar el inventario en el excel hay algunos productos que no figuran, es decir, yo tengo el prod 001, 002, 003 y luego me salta al prod 007, que función tengo que usar para me vaya auto complentando los productos que faltan en este caso el 004, 005 y 006...


El objetivo es claro, que se autorrellenen exclusivamente las celdas vacías o en blanco con el valor deseado.
Para ello deberemos selccionar, en primer lugar, todo el rango de datos (vacíos o con valor).
En segundo lugar activaremos la herramienta Ir a, presionando la tecla de función F5, bien Ctrl+i o bien navegando desde la Ficha Inicio > Grupo Modificar > Buscar y seleccionar > Ir a (o Ir a Especial)


Si partimos de nuestro rango de datos:


Tras 'Aceptar' veremos como de nuestro rango de celdas sólo se encuentran seleccionadas aquellas sin datos; sin seleccionar nada más, iremos a la Barra de fórmulas e introduciremos la siguiente fórmula:
=FILA()-1
y la ejecutaremos matricialmente, esto es, presionando al tiempo Ctrl + Enter:


Esta fórmula es válida para nuestro caso, en el que necesitamos que se genere un autonumérico, que para el ejemplo planteado corresponderá al número de fila a rellenar menos uno.
El resultado obtenido:

14 comentarios:

  1. no me funcionó, ¿será que en excel 2003 no aplica?, me aparecio =FILA()-1 en lugar de los numeros de la secuencia
    gracias
    Saludos
    Alexx
    Los Mochis, Sinaloa, México

    ResponderEliminar
  2. Hola Alexx,
    no, lo explicado es válido para todas las versiones, también para 2003.
    Es algo raro que no te funcione, te has asegurado de ejecutarlo matricialmente, esto es, presionando Ctrl+Mayusc+Enter, es lo único que se me ocurre... salvo que tengas seleccionado para ver las fórmulas en lugar de los resultados. Presiona Alt+º por si acaso.
    Slds

    ResponderEliminar
  3. no funciono porque seguro tenia el formato de las celdas en texto, debe ser en número

    ResponderEliminar
  4. Hola.
    Necesito de una lista (inventario) como hay saltos entre los códigos de artículos, cuando los paso a excel rellenarlos, para poder comparar la lista. Ejemplo
    COD. DESCRIPCIÓN STOCK
    001 mueble marron 32
    002 mueble tipo B 1
    004 silla 4
    006 mesa A 5

    Tengo que crear las filas 3 y 6.
    Pero además de incrementar los códigos que incremente toda la fila, para que cada código vaya con el texto y stock.

    Muchas gracias
    José Luis

    ResponderEliminar
    Respuestas
    1. Hola José Luis,
      puedas echar un vistazo a esta entrada
      http://excelforo.blogspot.com.es/2011/09/vba-como-insertar-filas-el-metod-insert.html
      En ella encontrarás unas macros que te insertarán y numerarán las filas inexistentes...
      Luego ya podrás completar Descripción y Stock para ellas.
      Espero te sirva.
      Slds

      Eliminar
    2. Me pongo a ello. Por lo que he visto me puede servir.

      Gracias

      Eliminar
    3. Perfecto!
      ya mee contarás...
      Slds

      Eliminar
    4. Como podría ejecutar esta macro que se adapta a mi proyecto, pero en 56 hojas excepto la hoja datos
      La macro es excelente pero no se como modificarla. Necesito su ayuda por fa

      Sub RellenaCeldasenBlanco()
      Dim hoja As Worksheet
      Dim UltFila As Long
      Dim Rng As Range

      Set hoja = Sheets("Hoja1")
      With hoja

      'Encontramos la última fila con valores
      UltFila = .Range("A" & .Rows.Count).End(xlUp).Row
      'creamos el rango
      Set Rng = .Range("A1:A" & UltFila)
      End With

      'formulamos las Celdas en Blanco para que tomen el valor de la celda anterior +1
      Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"

      'liberamos las variables de objeto
      Set hoja = Nothing
      Set Rng = Nothing

      End Sub

      Eliminar
    5. Hola Oscar, un placer saludarte igualmente...
      para ejecutar sobre todas las hojas de tu Libro excepto la que se llame 'Datos', podrías aplicar un bucle que recorra todas las hojas, añadiendo una condición IF ..THEN que controle la NO ejecución en la hoja 'Datos'.

      For i = 1 To Worksheets.Count
      If Sheets(i).Name <> "Datos" Then
      '... resto código

      End If
      Next i

      Ojo de adecuar tu código a la hoja concreta del recorrido!!:
      Set hoja = Sheets(i)

      Saludos

      Eliminar
    6. Gracias ismael por tupronta respuesta, una consulta mas, en mi caso para aplicar la formula Rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1", solo necesito, aplicarl en todas las hojas en el rando de (D11:D50), he tratado de modificarla pero me da errores, por fa dame una manito, porque no puedo. gracias.

      Eliminar
    7. si sólo necesitas aplicarlo en el rango D11:D50, entonces al definir la variable objeto
      Set Rng = .Range("A1:A" & UltFila)

      cámbialo directamente por
      Set Rng = .Range("D11:D50")

      con eso bastaría...
      Saludos

      Eliminar
    8. Gracias Ismael, era tan sencillo,pero así es cuando no se sabe.
      Mil gracias, me has ayudado mucho. buena suerte.

      Eliminar
  5. hola Ismael

    siguiendo tu codigo de relleno en las celdas

    yo tengo un archivo de excel que todo lo tengo hecho en condicionales establecido unos valores que deben ejecutarse debido a la edad del usuario
    entonces que quisiera que me ayudaras.

    como puedo ingresar un bucle que recorra todas esas filas y columnas y dentro del bucle un condicional que me compare calculando la fecha de nacimiento me arroje la edad
    que tiene esa persona y debido a la edad me imprima el valor que desee
    un ejemplo

    A1 tipo documento = CC
    B1 numero documento = 234346534
    C1 primer nombre = CALDO
    D1 segundo nombre = POLLO
    E1 fecha de nacimiento = 11/08/1985
    F1 SEXO = MASCULINO
    G1 EDAD = la edad debo calcularla con la fecha de nacimiento
    H1 DATOS = de acuerdo con la edad y comparando con el sexo de la
    persona que si es menor o mayor o igual, o si es
    femenino o masculino me imprima el dato dependiendo
    al condicional

    ResponderEliminar
    Respuestas
    1. Hola Deyver,
      si tienes que ir fila a fila (registro a registro) y lo que debes cambiar o calcular con fórmula son G1 (Edad) y Datos ( H1), no creo necesario ninguna macro, basta con unas sencillas fórmulas que luego arrastrarías hacia abajo.
      Para la edad lee este post:
      http://excelforo.blogspot.com.es/2010/05/calcular-la-edad-con-excel.html
      Para la celda H1 añadiría un condicional:
      =SI(Y(G1>18;F1="Masculino");"texto";....)

      No pedo precisar más son conocer las condiciones.
      Saludos

      Eliminar

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