To loop or not to loop (for speed)

[Lallo]

Hello world!

In a (sometimes quite large) range, I need to "weed out" values that are less than the Integer "X" (given by the user from a User Form). Cells contain formulas originally, but most formulas already return "". Formulas need not be preserved.

The following code works, but is excruciatingly slow.

For Each Cell In Selection
If Cell.Value < X Then
    Cell.Value = ""
    End If
Next Cell

Any way to speed things up?

 

[JoeMo]

Hello world!

In a (sometimes quite large) range, I need to "weed out" values that are less than the Integer "X" (given by the user from a User Form). Cells contain formulas originally, but most formulas already return "". Formulas need not be preserved.

The following code works, but is excruciatingly slow.

For Each Cell In Selection
If Cell.Value < X Then
    Cell.Value = ""
    End If
Next Cell

Any way to speed things up?

Try:

For Each Cell in Selection.SpecialCells(xlCellTypeFormulas, xlNumbers)

 

[Jonmo1]

A good way to speed up code is to turn off calculation.
Especially when you have alot of formulas..

Try

Application.Calculation = xlCalculationManual
 
For Each Cell In Selection
    If Cell.Value < X Then
        Cell.ClearContents
    End If
Next Cell
 
Application.Calculation = xlCalculationAutomatic

Also, I prefer Cell.ClearContents over Cell.Value = ""


Hope that helps.

 

[Lallo]

SpecialCells, you old thing, you!

Thanks, works faster for sure. Any further improvement would be welcome though...

 

[sous2817]

eek! Just read the "less than" bit...ignore me

 

[chirp]

how large is the maximum size of your range?

 

[chirp]

this is a looping method as well, but converts to a variant array first.

will run into memory problems on large ranges (depending on computer/version)...this uses a "isBetween" function, which could be removed, or moved inline, as you only need to compare the upper bound

handles multiple ranges, uses like:

sub test()
call del_nums(range("a1:a100000"),12)
end sub

Public Function del_nums(inputRange As Range, _
                        Optional numHi As Double = 1E+308, _
                        Optional numLow As Double = -1E+308, _
                        Optional inclusive As Boolean = True, _
                        Optional tst As Boolean)
                        
Dim tmpArr, tmpVar, i As Long, j As Long, tmp As Long
Dim ar As Range

tst = False
On Error GoTo exitFunc
If inputRange Is Nothing Then GoTo exitFunc
If numHi < numLow Then GoTo exitFunc

For Each ar In inputRange.Areas
    If ar.Cells.count > 1 Then
        tmpArr = ar.Value2
        tmp = UBound(tmpArr, 2)
        
        If tmp > 1 Then 'eliminate inner loop for single col
            For i = 1 To UBound(tmpArr)
                For j = 1 To tmp
                    If IsNumeric(tmpArr(i, j)) Then
                        If Not isBetween(CDbl(tmpArr(i, j)), numLow, numHi, inclusive) Then tmpArr(i, j) = vbNullString
                    End If
                Next
            Next
        Else
            For i = 1 To UBound(tmpArr)
                If IsNumeric(tmpArr(i, 1)) Then
                    If Not isBetween(CDbl(tmpArr(i, 1)), numLow, numHi, inclusive) Then tmpArr(i, 1) = vbNullString
                End If
            Next
        End If
        ar = tmpArr
    ElseIf IsNumeric(ar.Value2) Then
        If Not isBetween(CDbl(ar.Value2), numLow, numHi, inclusive) Then ar = vbNullString
    End If
Next
tst = True
exitFunc:
End Function

Public Function isBetweenD(test As Double, numLow As Double, numHigh As Double, _
                        Optional inclusive As Boolean = True) As Boolean

On Error GoTo exitFunc
    If inclusive Then
        If test >= numLow Then
            If test <= numHigh Then isBetweenD = True
            Exit Function
        End If
    Else
        If test > numLow Then
            If test < numHigh Then isBetweenD = True
            Exit Function
        End If
    End If
exitFunc:
End Function

 

[Lallo]

Thanks all! The helpfulness of strangers never ceases to amaze!

In the end the code I used was this:

Application.Calculation = xlCalculationManual
 
For Each Cell In Selection
    If Cell.Value < X Then
        Cell.ClearContents
    End If
Next Cell
 
Application.Calculation = xlCalculationAutomatic

Also, I prefer Cell.ClearContents over Cell.Value = ""


Hope that helps.

Quite simple, and fast enough for the job it turns out.

Thanks again!

/lallo

 

[Rick Rothstein]

In the end the code I used was this:

Quite simple, and fast enough for the job it turns out.

If I had to guess, I would say this is probably the fastest code you will find for what you want to do...

Sub WeedTheRange()
  Dim R As Long, C As Long, Arr As Variant
  
  '  Get the value of X in whatever way you do now,
  '  but for example purposes (no error testing)...
  Dim X As Double
  X = InputBox("Give me a value:")
 
  Arr = Selection.Value
  For R = 1 To UBound(Arr)
    For C = 1 To UBound(Arr, 2)
      If Arr(R, C) < X Then Arr(R, C) = ""
    Next
  Next
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Selection = Arr
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Just put your current method of getting X in place of the part I marked off.

 

[Lallo]

@Rick Rothstein

Thanks for that! Works fine too.

At the moment I'm testing with partial data, so no difference can be noticed in terms of speed. But I'm sure it will help in "hot" conditions... and at any rate I've gained some skills!