I85PREP
⌘ K
DOMINA-BACH Básico study materials
DOMINA-BACH Básico study resources

Borrado de filas: índices que se desplazan y celdas ocultas

DOMINA-BACH Básico · Put your knowledge to work in context.

All case studies

Borrado de filas: índices que se desplazan y celdas ocultas

La hoja «Pedidos» de un mayorista tiene 45.000 filas y ocho columnas. Marta debe eliminar las filas cuya columna C esté vacía o contenga «ANULADO» y después marcar en la columna H los pedidos superiores a 5.000 €. Su primer procedimiento recorre con For i = 2 To ultimaFila, comprueba la condición y ejecuta Rows(i).Delete sin más. Al terminar comprueba que siguen ahí parejas de filas «ANULADO» consecutivas. El proceso tarda unos seis minutos, la pantalla parpadea y la barra de estado no deja de moverse. En una segunda versión aplicó AutoFilter sobre la columna C y recorrió con For Each celda In Range("C2:C45000") borrando la EntireRow de cada celda cuyo valor fuera «ANULADO»; el resultado incluyó filas que no cumplían el criterio y que el filtro tenía ocultas.

1. ¿Por qué el primer procedimiento deja sin borrar filas «ANULADO» consecutivas? Ilústralo con índices concretos.

Read the worked answer

Porque al borrar se desplaza lo que aún no se ha revisado. Supongamos que las filas 10, 11 y 12 tienen «ANULADO» en la columna C.

  • i = 10: se borra la fila 10. La antigua fila 11 pasa a ocupar la 10 y la antigua 12 ocupa la 11.
  • Next i incrementa a 11. La fila 11 es ahora la antigua 12, que sigue sin evaluar respecto al criterio anterior, y la antigua 11 (que también era «ANULADO») ha quedado en la fila 10, ya visitada.
  • Resultado: se borra una de cada dos filas consecutivas que cumplen la condición.

Soluciones posibles, por orden de sencillez.

  1. Recorrer al revés: For i = ultimaFila To 2 Step -1. Al borrar hacia arriba, los índices de las filas pendientes no cambian, con lo que no se salta ninguna.
  2. No borrar dentro del bucle: acumular las filas en un Union y ejecutar un único Delete al final, también de abajo arriba.
  3. Filtrar y borrar solo las visibles, o reescribir la tabla desde una matriz en memoria.

Comprobación de que el error es de lógica, no de Excel: si se ejecuta el procedimiento dos o tres veces seguidas y cada pasada elimina más filas «ANULADO», es exactamente este patrón. Un procedimiento de limpieza debe dar el mismo resultado la primera vez que la quinta.

2. ¿Por qué el `For Each` sobre `C2:C45000` borró filas que el filtro tenía ocultas? ¿Cómo se recorre solo lo visible?

Read the worked answer

Ocultar no es excluir del rango. AutoFilter cambia la altura de las filas a cero y las marca como ocultas, pero el objeto Range("C2:C45000") sigue conteniendo las 44.999 celdas. For Each recorre la colección completa, en orden, incluidas las ocultas. Si una fila oculta tiene la columna C vacía, la condición se cumple y EntireRow.Delete la elimina, aunque el usuario no la estuviera viendo.

Cómo recorrer solo las visibles.

  • Range("C2:C45000").SpecialCells(xlCellTypeVisible) devuelve únicamente las celdas visibles. Ojo: si el filtro no deja ninguna, lanza el error 1004; hay que comprobarlo antes con On Error Resume Next acotado o mediante el recuento de Areas.
  • Ese rango visible puede estar formado por varias áreas discontinuas: no se puede asumir Cells(1), Cells(2) como filas consecutivas. Lo habitual es recorrer For Each area In rangoVis.Areas y dentro For Each celda In area.
  • Sigue existiendo el problema del desplazamiento al borrar. Aunque solo se recorran visibles, cada Delete renumera las filas y las áreas calculadas antes dejan de ser válidas.

Estrategia recomendada. Construir con Union las EntireRow visibles que cumplen el criterio, ordenar el borrado de abajo arriba o confiar en que Union(...).EntireRow.Delete elimina todas de una vez, y desactivar el filtro antes de borrar para evitar estados inconsistentes. Alternativa más limpia: leer la tabla en una matriz, filtrar en memoria y volcar el resultado en una hoja nueva.

3. El proceso tarda unos seis minutos y la pantalla parpadea. Explica de dónde sale ese tiempo y propón una estrategia de mejora indicando los riesgos.

Read the worked answer

De dónde sale el tiempo. Cada instrucción sobre la hoja es una llamada al motor de Excel: leer una celda, escribir, borrar. Un Delete de fila obliga además a renumerar y recalcular lo que hay debajo, y si el cálculo está en automático, cada borrado arrastra la recalculación de fórmulas. Con 45.000 iteraciones y varios cientos de borrados, el coste se multiplica. El parpadeo es el repintado de pantalla tras cada operación.

Palancas, por orden de impacto.

  1. No borrar fila a fila: acumular en Union y un solo Delete, o reescribir desde una matriz. Es el cambio que más reduce el tiempo.
  2. Application.ScreenUpdating = False y Application.Calculation = xlCalculationManual al principio; restaurar ambos al final.
  3. Eliminar Select, Activate y ActiveCell del bucle: no son operaciones de datos y arrastran cambios de foco.
  4. Leer el rango completo en una variable tipo Variant (datos = rango.Value), procesar en memoria y volcar de una vez.

Riesgos que hay que controlar. Si el procedimiento falla a mitad y no hay manejador de errores, ScreenUpdating se queda en False y Calculation en manual: la aplicación parece rota y nadie sabe por qué. Por eso el bloque de restauración va en la etiqueta final, con On Error GoTo Salir y Salir: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic.

Expectativa realista: con estas medidas, 45.000 filas pasan de minutos a segundos, pero conviene medir con Timer antes y después en lugar de fiarse de la impresión.