Faster method to clear range of Rows on condition,other than using AutoFilter or For Each Loop?-CodePudding

supposing I have a lot of values on Column_H.
I need to search for the value="Close" on that column and if found then clear the entire rowcontaining that value. Note: using Autofilter method is not applicable (for some reasons).
Now,I am using for each loop as the below code, and it is iterates a bit slowly on large ranges.
is there another methods can do that faster on one shot? In advance,welcome with any useful help.

Option Explicit
Option Compare Text
Sub Search_Clear()
    With Application
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
       .EnableEvents = False
    End With

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("SheetB")
    Dim StatusColumn As Range: Set StatusColumn = ws.Range("H2", ws.Cells(Rows.Count, "H").End(xlUp))
    
 Dim cell As Object
 For Each cell In StatusColumn
   If cell.Value = "Close" Then cell.EntireRow.Clear
    Next cell

   With Application
      .Calculation = xlCalculationAutomatic
      .ScreenUpdating = True
      .EnableEvents = True
    End With
End Sub

CodePudding user response：

You can increase the code speed from two directions. Iteration in an array is faster than doing it in a range, and more important, do not clear every row at a time. A Union range should be used:

Dim arr, rngClear as Range, i as Long
arr = StatusColumn.Value
for i = 1 to Ubound(arr)
    if arr(i,1) = "Close" Then
       if rngClear Is Nothing Then
          set rngClear  = StatusColumn.Cells(i)
       else
          set rngClear  = Union(rngClear, StatusColumn.Cells(i))
       end if
    end if
next i
'then clear them at the end:
If not rngClear Is Nothing Then rngClear.EntireRow.ClearContents

In your question of yesterday, if I remember well, the above range was already iterated, meaning that a second step (the above one) should not be necessary. The Union range should be created in that first (existing) iteration...

CodePudding user response：

Try to read once a whole column instead of multiple cell reads:

Dim StatusValue, i As Long
 StatusValue = StatusColumn.Value
 For i = LBound(StatusValue) To UBound(StatusValue)
  If StatusValue(i, 1) = "Close" Then ws.Rows(i   1).Clear
 Next i