Clear rows with an empty cell in column A, without deleting entire row takes a very long time

Question

This code is a part of a larger macro.

Sub testremoveBlankRows()

Dim rng8        As Range
Dim cell        As Range
'------------------------------
'Start Timer
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
'-------------------------------------------------
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
    .CutCopyMode = False
End With
'--------------------------------------------------
ActiveSheet.UsedRange
On Error Resume Next
Set rng8 = Columns("A").SpecialCells(xlBlanks)
On Error GoTo 0

If rng8 Is Nothing Then Exit Sub
    For Each cell In rng8.Areas
        cell.Cells(1).Offset(0, 0).Resize(cell.Rows.count, 24).Delete xlUp
    Next cell
'-------------------------------------------------------------
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .EnableEvents = True
    .CutCopyMode = False
End With
'-------------------------------------------------------------
'Stop Timer
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
'-------------------------------------
End Sub

This piece of code takes about 85 seconds to run (Sheet1), if I use it in the macro. If I run code separately (Sheet1), it still takes about 85 seconds to run. If I open a new Worksheet in original Workbook and copy/paste values, run code separately, it still takes about 85 seconds to run. If I open a new Workbook and copy/paste values from Sheet1, it takes 0,49 seconds!

What can I do to have it run in 0,49 seconds in the original Workbook?

Create a range to delete using `Union` and delete *once*, after looping. There are many examples on SO, one such example is [here](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba). — BigBen, Feb 17 '22 at 15:40
@ Scott Craner Thanks for reply. "rng8.resize(,24).Delete" = Run-time error 1004, Application-defined or object-defined error. With/Without Application.Calculation = xlManual. Intersect(rng8.EntireRow, Range("A:X")).Delete = 85 sec. — Soidog, Feb 18 '22 at 11:57
@Patrick Honores Thanks for reply. cell.Resize(1, 24).Delete xlUp '82 sec. but only removes one empty cell(Col.A) to Col.X. expl.A: If A3 = blank, A3:X3 is deleted(OK). But if A6:A9 = blanks, only A6:X6 is deleted. Next blanks = A15:A16, only A15:X15 is deleted etc.. No difference With/Without Application.Calculation = xlManual — Soidog, Feb 18 '22 at 12:00
@BigBen Moderator deleted my answer. I tried code from https://stackoverflow.com/questions/47872426/for-each-loop-wont-delete-all-rows-with-specific-values/47873216#47873216. I used: If Not toDelete Is Nothing Then Intersect(toDelete.EntireRow, Range("A:X")).Delete (because I can not delete entire row). My solution (1,6 sec.), where I use Workbooks.Add, is not optimal. Any suggestions? — Soidog, Feb 22 '22 at 10:10

score 0 · Answer 1 · answered Feb 17 '22 at 16:49

I would sort on col A and the delete all the rows at once.
Otherwise, if you need to keep the current logic I would turn calculation to Manual during that part Application.Calculation = xlManual (since you mentioned that it takes only 1/2 sec when you copy/paste values in a blank workbook).
And I would rewrite

cell.Cells(1).Offset(0, 0).Resize(cell.Rows.count, 24).Delete xlUp

as

cell.Resize(1, 24).Delete xlUp

or perhaps

cell.EntireRow.delete

Clear rows with an empty cell in column A, without deleting entire row takes a very long time

1 Answers1