lunes, 20 de junio de 2011

Eliminar Filas y Columnas vacias

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

18 comentarios:

vvcepheus7 dijo...

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!

vvcepheus7 dijo...

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!

Adolfo Aparicio dijo...

Hola vvcepheus7.

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

Eliminar filas con cierta periodicidad en #Excel

Un cordial saludo.

vvcepheus7 dijo...

Muchísimas gracias por la macro Adolfo! funciona genial!

udebruyn dijo...

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...

Valeria dijo...

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

Paco Vela. dijo...

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.

Adolfo Aparicio dijo...

Paco, Felicidades por el aporte.
Muy bueno.

SoportePitti dijo...

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.

Adolfo Aparicio dijo...

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.

chico merida dijo...

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

anibal pinto dijo...

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

2Leo dijo...

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

muchas gracias

Adolfo Aparicio dijo...

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.

Alex dijo...

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

charles chaplin dijo...

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.

VICO dijo...

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


Javier Ortiz Quintero dijo...

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