Completar los espacios en blanco

Archivo de Excel utilizado: completar.xlsm

En esta entrada vamos a utilizar en tres casos la tecla F5 que es Ir a y luego el botón Especial...

Hoja1

Utilizaremos dos métodos para completar los espacios en blanco que hay debajo de ciertas palabras.

El resultado obtenido es el siguiente.

También podemos utilizar Macros.

Para que la macro funcione correctamente primero se ha de seleccionar el rango E4:E23.
Observe el código que con Set Rng = Secection lo que hacemos es recogen en la variable objeto Rng el rango que previamente teníamos seleccionado. De este rango deseamos obtener la fila y columna de inicio que es la fila 4 y columna 5, y el número de celdas que son 20. Ahora ya podemos trabajar con estos valores y meternos en un For...Next que nos permitirá recorrer el rango y analizar si la celda está vacía o no. Si está vacía hacemos que tome el valor de la previa.

Sub Completa()
Dim Rng As Range
Dim FilIn As Long 'Fila de inicio del rango
Dim ColIn As Integer 'Columna de inicio del rango
Dim NumCeldas As Long
Dim Fila As Long
Set Rng = Selection
FilIn = Rng.Application.ActiveCell.Row
ColIn = Rng.Application.ActiveCell.Column
NumCeldas = Rng.Cells.Count
For Fila = FilIn To FilIn + NumCeldas - 1
  If Cells(Fila, ColIn).Value = Empty Then
    Cells(Fila, ColIn).Value = Cells(Fila - 1, ColIn).Value
  End If
Next Fila
End Sub

Hoja2

Deseamos obtener un resumen de la siguiente tabla.

 El resumen que pretendemos obtener es el siguiente.

Para conseguir nuestro objetivo realizaremos los dos procedimientos siguientes. Con el primero obtendremos un listado de ciudades y con el segundo las sumas que corresponden a cada ciudad.

Hoja3

Para eliminar filas en blanco ya se realizó un procedimiento con macro en otro post cuyo enlace es el siguiente.

Eliminar Filas y Columnas vacías

En esta ocasión vamos a eliminar filas sin usar una macro. Existe una limitación de este método que consiste en que eliminará la fila donde todas las celdas estén vacías, pero también se eliminarán las filas que contengan alguna celda vacía aunque no queramos. Esto es, si alguna fila tiene datos y no deseamos eliminarla pero contiene una celda vacía, simplemente por existir una celda vacía eliminará la fila completa, aunque esta tenga otros datos.

Eliminar Filas sin Macro

Descargar el archivo excelavanzado_eliminar_filas_sin_macro.xlsx

Ya disponemos de un post en este blog que nos cuenta cómo

• Eliminar Filas con Macro

Relacionado con este tema también disponemos de otras dos entradas.

• Eliminar Filas y Columnas vacías
• Eliminar filas con cierta periodicidad

También hemos trabajado ocultando columnas con macro. Lo que se hacía es marcar algunas celdas de un cierto color y luego ocultar las columnas que corresponden a esas celdas. Modificando un poco la macro en lugar de eliminar columnas se podrían eliminar filas.

• Ocultar columnas marcadas con un color

En esta ocasión vamos a ver cómo eliminar filas sin necesidad de usar una macro. Si son pocas las filas a eliminar se pude hacer de forma manual sin mayores problemas. Para ello marcamos el indicador de la primera fila y luego, mientras pulsamos la tecla CONTROL, marcamos el indicador del resto de filas. Así quedarían seleccionadas todas las filas que deseamos eliminar. Luego, pulsamos con el botón derecho del ratón sobre cualquiera de las filas marcadas y elegimos Eliminar. De esta forma eliminaríamos las filas de forma manual.

Cuando tenemos que eliminar muchas filas dispersas a lo largo de una tabla debemos recurrir a una macro o realizar el truco que se describe en el siguiente vídeo.

Eliminar filas con cierta periodicidad

Descargar el fichero: elimina_filas.xlsm

Para eliminar filas que se repiten con cierta periodicidad hemos diseñado una macro. Es frecuente que al importar informes de otras aplicaciones obtengamos un texto plano que al volcarse a Excel lleva muchas líneas de "basura" que deseamos eliminar. Si las filas que deseamos suprimir se repiten de forma periódica podemos utilizar la macro que explicaremos seguidamente.

Ya habíamos hablado de eliminar filas y columnas vacías en otro post:

Eliminar Filas y Columnas vacias

En esta ocasión lo que pretendemos es eliminar ciertas filas que se repiten periódicamente.

En nuestro ejemplo disponemos de un texto que comienza en la fila 4, donde se alternan algunas filas que nos resultan válidas (color azul) y otras que nos resultan inútiles (color rojo). La macro se lanza pulsando el botón que hemos incrustado, y lo primero que hace es preguntarnos por la primera fila válida, y finalmente nos pregunta por la segunda fila válida. La propia macro calcula el final del texto en esa hoja y procede a eliminar las filas inútiles.

La macro no la hemos colocado en un módulo como es habitual sino en ThisWorkbook. Véase la imagen siguiente.

Es importante observar que la macro comienza a eliminar de abajo hacia arriba, ya que en caso contrario, se iría alterando el número que ocupan las filas, al ser eliminadas.

Código:

Sub elimina_filas_inutiles()
Dim primera As Long
Dim segunda As Long
Dim ultima As Long
Dim i As Long
primera = InputBox("Indique la primera fila válida", , 4)
segunda = InputBox("Indique la segunda fila válida", , 11)
ultima = ActiveCell.SpecialCells(xlLastCell).Row
For i = ultima To primera Step -1
    If (i Mod (segunda - primera)) - primera <> 0 Then
        Cells(i, 1).EntireRow.Delete
    End If
Next i
End Sub

La clave del proceso está en el operador Mod que es el módulo. En español se denomina resto, que se corresponde con la función RESIDUO de Excel.

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