lunes, 20 de junio de 2011

Eliminar Filas y Columnas vacías

Descargar el fichero: LimpiaFilas.xlsm


Es frecuente que un informe generado por otro programa se exporte a Excel, por ejemplo en  SAP. Muchos programas contemplan la exportación directa a Excel, y en otras ocasiones lo que se hace es generar un fichero plano, de texto. Sea por un método o por otro, habitualmente los informes contienen filas vacías, y columnas vacías. Presentamos una macro que elimina las filas y las columnas en blanco, dejando el informe compactado.

Hoja 1



Hoja 2

La macro que elimina las filas vacías es la siguiente.


En la macro recorremos todas las filas del rango usado (UsedRange). Se basa en detectar el número de celdas vacías con la función CountA, que en español se denomina CONTARA. Esta función es similar a la función CONTAR. La diferencia es que CONTAR cuenta únicamente valores numéricos, y CONTARA cuenta todo tipo de celdas, sean estas numéricas o de texto.

Creamos la variable Cadena que por concatenación va acumulando todas las filas vacías que se eliminarán. Al concatenar las diferentes filas las separamos por una coma (,) lo que hace que al final tengamos que eliminar la última coma, cosa que se hace con la expresión:
 Left(Cadena, Len(Cadena) - 1).

Esta macro aplicada a hojas en las que sea necesario eliminar muchas filas puede llegar a dar un error derivado de que la variable Cadena necesita una lóngitud mayor de la que es capaz de soportar. Para evitar ese inconveniente hemos creado la macro de la hoja 3 que también elimina las filas vacías, pero lo hace cada vez que las localiza.

Código:

Sub EliminarFilasVacias()
Dim Cadena As String, Fila As Long
With Worksheets("Hoja2") 'Nombre de la hoja
    For Fila = 1 To .UsedRange.Rows.Count
        If WorksheetFunction.CountA(.Rows(Fila)) = 0 Then
            Cadena = Cadena & Fila & ":" & Fila & ","
        End If
    Next Fila
    If Cadena <> "" Then
        Application.ScreenUpdating = False
        .Range(Left(Cadena, Len(Cadena) - 1)).Delete
        Application.ScreenUpdating = True
    End If
End With
End Sub
Esta macro se aplica a la Hoja2 que es la siguiente. Se lanza con un botón que hay en la parte superior izquierda de la hoja.


Hoja 3

En la Hoja3 disponemos de una macro que elimina las filas vacías y otra que elimina la columnas vacías.

La macro que elimina las filas vacías es la siguiente. Es similar a la que hemos utilizado en la Hoja 2.

Código:

Sub EliminarFilasEnBlanco()
Dim n As Long 'nº filas
Dim i As Long
Dim Fila As String
n = ActiveSheet.UsedRange.Rows.Count
For i = n To 1 Step -1
   Fila = i & ":" & i
   If WorksheetFunction.CountA(Range(Fila)) = 0 Then
      Range("A" & i).EntireRow.Delete
   End If
Next i
End Sub
Hemos necesitado hacer el bucle desde n hasta 1 ya que si lo hacemos de 1 a n en caso de existir dos o más filas vacías contiguas quedaría una de ellas sin eliminar.

Para eliminar las columnas vacías existe una macro similar. Es la siguiente.

Código:

Sub EliminarColumnasVacias()
Dim n As Integer 'nº columnas
Dim i As Integer
Dim col As String
n = ActiveSheet.UsedRange.Columns.Count
For i = n To 1 Step -1
    If WorksheetFunction.CountA(Cells(1, i).EntireColumn) = 0 Then
        Cells(1, i).EntireColumn.Delete
    End If
Next i
End Sub

Otra variante para eliminar filas en blanco es la siguiente.

Código:

Sub EliminarFilasEnBlanco_bis()
Dim Fila As Long
For Fila = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If WorksheetFunction.CountA(ActiveSheet.Rows(Fila)) = 0 Then
        Cells(Fila, 1).EntireRow.Delete
    End If
Next Fila
End Sub

Si no deseamos que se vea el movimiento en pantalla que origina la eliminación de filas podemos añadir al inicio de la macro (después de los Dim) la línea:
  • Application.ScreenUpdating = False
Y al final de la macro pondríamos la línea:
  • Application.ScreenUpdating = True
Esto hace que mejore el tiempo de ejecución, apreciándose en hojas de muchísimas filas.

Pulsando sobre el desplegable de la hoja 3 podemos generar un informe con filas y columnas en blanco que podemos hacer desaparecer eligiendo las opciones adecuadas en el desplegable (ComboBox).


Otra variante que Elimina Filas Vacías

Sub Elimina_Filas_Vacias()
Dim n As Long 'nº filas
Dim i As Long
Dim Fila As String
n = ActiveSheet.UsedRange.Rows.Count
For i = n To 1 Step -1
Fila = i & ":" & i
If WorksheetFunction.CountA(Range(Fila)) = 0 Then
  Range("A" & i).EntireRow.Delete
End If
Next i
End Sub

28 comentarios:

  1. Hola!

    Quisiera hacerle una consulta!

    Tengo un excel con un formato como este:

    A B
    1 DIRECCIÓN | CIUDAD
    2 TEXTO INÚTIL
    3 TEXTO INÚTIL
    4 TEXTO INÚTIL
    5 TEXTO INÚTIL
    6 TEXTO INÚTIL
    7 TEXTO INÚTIL
    8 DIRECCIÓN | CIUDAD
    9 TEXTO INÚTIL
    10 TEXTO INÚTIL
    11 TEXTO INÚTIL
    12 TEXTO INÚTIL
    13 TEXTO INÚTIL
    14 TEXTO INÚTIL
    15 DIRECCIÓN | CIUDAD
    (...)

    Para clarificar he enumerado cada fila y cada columna.

    Yo deseo eliminar todas las filas inútiles (donde hay texto inútil) y juntar todas las filas útiles (o sea, las de las direcciones).
    Si se fija, se repite que hay 6 filas inútiles y una fila útil.

    Como podría hacerlo Con excel?

    También tengo el documento txt con este mismo formato, se podría hacer algo parecido con el word usando buscar y reemplazar?

    Muchas gracias y un saludo!

    ResponderEliminar
    Respuestas
    1. Hola,

      Hay una forma mas simple para poder eliminar las filas vacías dentro de una base de datos, el siguiente código es para una versión de Excel 2010:

      Sub suprimir()
      'Determinar la cantidad de elementos a procesar
      ult = Cells(Rows.Count, 1).End(xlUp).Row
      'recorrer todos los elementos
      For x = ult To 1 Step -1
      'condicion a evaluar
      If Application.CountA(Rows(x)) = 0 Then
      'Accion a realizar
      Rows(x).Delete
      End If
      Next
      End Sub

      Este código va buscando las filas vacías desde abajo y las va eliminando.

      Eliminar
  2. He hecho un intento:

    Sub EliminaFilas()
    'Elimina 6 filas y salta una comenzando en la fila 2

    For i = 2 To 1000
    Cells(i, "A").EntireRow.Delete
    Cells(i + 1, "A").EntireRow.Delete
    Cells(i + 1, "A").EntireRow.Delete
    Cells(i + 1, "A").EntireRow.Delete
    Cells(i + 1, "A").EntireRow.Delete
    Cells(i + 1, "A").EntireRow.Delete
    Next i

    End Sub

    Pero parece que no funciona!

    Me puede echar una mano? :)
    Muchas gracias!

    ResponderEliminar
  3. Hola vvcepheus7.

    Te he creado un post que creo puede resolver tus necesidades.

    Eliminar filas con cierta periodicidad en #Excel

    Un cordial saludo.

    ResponderEliminar
  4. Muchísimas gracias por la macro Adolfo! funciona genial!

    ResponderEliminar
  5. Me parece muy bueno.
    Solo una pregunta: ademas de eliminar filas vacias me gustaria eliminar filas que contengan formulas de las cuales dan resultado cero. Y obviamente las que esten mayor que cero dejarlas.
    Muchas gracias...

    ResponderEliminar
  6. Muchas Gracias,he utilizado la macro de la hoja 3 para un archivo grande y me ha sido de mucha ayuda.

    ResponderEliminar
  7. Una aportación para los que no quieran utilizar macros y prefieran hacerlo "manual":

    - Seleccionamos la hoja entera, o el rango de filas que queramos, y le damos a F5 ("ir a").
    - Aquí le damos a la tecla "especial", y seleccionamos la opción "celdas en blanco". Con esto, tendremos seleccionadas las celdas en blanco en nuestra hoja.
    - Ahora vamos a "Eliminar" -> "Eliminar filas de la hoja", y esto nos eliminará las filas de las celdas seleccionadas.

    Chapucero, pero efectivo.

    Saludos.

    ResponderEliminar
  8. Paco, Felicidades por el aporte.
    Muy bueno.

    ResponderEliminar
  9. Hola de nuevo, he utilizado la macro de la hoja 3 en un archivo con 437 filas y al parecer todo bien pero al revisar mi archivo detecto que aún quedan 2 filas en blanco al final, es decir antes de mi última fila con dato, lo vuelvo a ejecutar y listo pero ¿por qué no las borró con la primera ejecución? En el tamaño de la variable veo que le asignas un "Long" y no se si se deba a que tenga un limite este tipo de variables o qué otra cosa puede ser. Intente sustituirlo por un "Worksheet" pero me pide la condición "With…End With".
    Mil gracias por tu ayuda.

    ResponderEliminar
  10. Hola SoportePitti.

    He revisado la macro de la Hoja3 que se llama Elimina_Filas_Vacias y funciona estupendamente en el ejemplo que tengo creado.

    Las variables son long que es un número entero en el rango -2.147.483.648 y 2.147.483.647. Esto es más que suficiente para identificar las posibles filas vacías.

    El problema que tienes probablemente es que tienes filas vacías en la parte superior de la hoja.

    Si pruebas la siguiente macro verás que se selecciona todo menos las filas vacías de arriba.

    Sub selecciona()
    Worksheets("Hoja3").Activate
    ActiveSheet.UsedRange.Select
    End Sub

    Por tanto puedes solucionar el problema eliminado las filas vacías de arriba o pasando dos veces la macro.

    Un cordial saludo.

    ResponderEliminar
  11. BUENAS NOCHES

    solicito su valios apoyo para ver si hay manera de eliminar las celdas vacias de un documento en excel
    ejemplo
    14:00 19:20
    14:40 17:20
    la idea es juntar los datos de muestra sin celdas intermedias vacias de manera automatica
    agradecemos su apoyo

    ResponderEliminar
  12. buen dia me puedes audar mi caso es eliminar la filas que contega un cero

    ResponderEliminar
  13. Excelente página, solo una duda, necesito hacer algo similar con las filas vacias, solo que necesito ocultarlas en vez de eliminarlas,

    muchas gracias

    ResponderEliminar
  14. Hola 2Leo.

    Toma la macro denominada EliminarFilasEnBlanco y sustituye donde dice EtireRow.Delete por EntireRow.Hidden = True

    Prueba eso a ver si te funciona bien.

    Puedes probar la sustitución también en EliminarFilasEnBlanco_bis

    Un saludo.

    ResponderEliminar
  15. Buena tarde, me sirvió bastante su aporte,, muchísimas gracias. Por otra parte, tengo una interrogante, la cual es còmo podría hacer para borrar celdas en blanco pero partiendo de una celda hacia abajo, es decir si tengo en la celda B5 "Nombre 1" C5 "Nombre 2" y debajo de las mismas varias datos algunos en blanco ¿cómo puedo borrar los mismos?

    Agradeciendo su gentil atención y a la espera de su respuesta, me despido.

    Saludos,

    Alex

    ResponderEliminar
  16. Hola buen dia excelentes ejemplos.
    estoy tratando de hacer algo similar quiero seleccionar un rango determinado y que de esa seleccion elimine las filas y las columnas en blanco.

    ResponderEliminar
  17. buenas tardes,

    Tengo en excel unas filas y columnas ocultas, me las deben mostrar la cantidad que sea ingresada por el usuario en una celda, realice esta macro para mostrar las filas y me funciona perfectamente, pero no se por que no me sirve al momento de utilizarla con columnas, no se si me puedas ayudar con esto. mil gracias

    Rows("8:107").Hidden = True

    Dim lngStart As Long
    Dim lngEnd As Long

    lngStart = 8
    lngEnd = ((Worksheets("Anzahl-MA").Range("F4").Value) + 7)

    Rows(lngStart & ":" & lngEnd).Hidden = False


    ResponderEliminar
  18. Adolfo muy buenas tardes: tengo una tabla que en las filas van nombres y en las columnas van fechas desde el 01 de ene hasta el 31 de dic. hay forma de con una macro mostrar solo un intervalo de fechas determinado y el resto ocultarlas?
    muchas gracias
    Javier

    ResponderEliminar
  19. consulta
    tengo varias tablas en una hoja del libro de excel y quiero una macro que me seleccione una tabla y para su rango, elimine filas de ella segun algun criterio. No me sirve que borre toda una fila porque modificaria las celdas de otra tabla que tiene ademas otras dimensiones en la hoja :S

    ResponderEliminar
  20. Hola buenos días, tengo una consulta y quisiera saber si me podrían ayudar, cada qna, tengo que "limpiar", una hojas de excel, con mas de 100 columnas, y 300 filas, lo que hago es realizar la sumatoria de cada columna y posteriormente eliminar las columnas que el total sea igual a cero, ¿cómo puedo hacer esto con una macro?, son 5 archivos diferentes a los que tengo que hacerle esto y todos ellos varian en cantidad de columnas y filas, pero a todos les tengo que realizar el mismo proceso, Gracias por sus aportes.

    ResponderEliminar
  21. Buenas tardes

    Quisiera saber si excel tiene la posibilidad eliminar las celdas vacías de un documento

    Muchas gracias por su colaboración

    ResponderEliminar
  22. Buen día:

    Tengo el siguiente problema.

    En la columna A y B tengo una lista de 100 productos y me gustaría que a traves de una macro buscara un producto en particular pero en algunas celdas estan en blanco tanto en al columna A y B, pero en si no es toda la fila completa que esta en blanco sino solo una celda , habra una Macro para que agregue en esa celda en blanco la leyenda "sin información" en automatico

    Gracias.

    ResponderEliminar
  23. ¡Hola!

    Tengo una tabla en la que la columna A siempre tiene datos y la columna N (sumatorio) también.
    En las columnas B a M puede haber datos o no.
    El objetivo es eliminar las filas completas, si de B a M todas las celdas están en blanco (si en alguna celda de ese rango hay algún dato, debe respetar la fila y no eliminarla).

    El código que estoy usando tiene algún fallo porque borra las filas, si en la columna B hay una celda en blanco, pero no está comprobando el resto del rango, por lo que si hay un dato en la columna C, también elimina la fila.

    ¿Alguien podría echarme una mano para que el código compruebe el rango completo?
    GRACIAS.

    El código que uso es el siguiente:

    Sub BorrarFilas()

    Dim Counter
    Dim i As Integer

    'Selección del rango a comprobar.
    ActiveSheet.range("B6:M6").Select

    ' Determinar el número total de filas en la hoja a partir de la columna A,
    ' ya que esta columna no tiene datos en blanco.

    Counter = Cells(5, "A").End(xlDown).Row

    ' Bucle a través del numero de filas.
    For i = 1 To Counter
    ' Comprobación para saber si las celdas activas están en blanco.
    If ActiveCell = "" Then
    Selection.EntireRow.Delete

    ' Reducir el contador cada vez que una fila se borra. Esto asegura
    ' que la macro no se pase de la última fila.
    Counter = Counter - 1
    Else
    ' Seleccionar las siguientes celdas.
    ActiveCell.Offset(1, 0).Select
    End If

    Next i

    End Sub

    ResponderEliminar
  24. Amigo una pregunta como realizo una macro que elimine las filas sin datos de la siguiente manera mi tabla "BD" tiene 10.000 datos, pero necesito que me evalue a partir de la celda "B10" en adelante ya alli si encuentra una celda sin dato la elimine

    ResponderEliminar