Faster method to clear range of Rows on condition,other than using AutoFilter or For Each Loop?
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 row
containing 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
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...