Macro to clear (a large number of) cells whose values are >1?

Question

Is it possible to clear a large number of cells based on a value, i.e. if >1? I am using Excel for Mac 2011.

I would like to convert thousands of values >1 to empty cells in a large dataset (600 rows x 450K). The values are supposed to range from 0 to 1, but there are errors scattered throughout where the entry is >1 (1000-10000) and precludes averaging rows accurately.

BTW: I tried the previously posted macro for "clear cells based on color" after highlighting all cells with values >1, but this failed. I am guessing because the RGB lookup table instructions don't match my version of excel? (Clear cell contents based on color?)

Answer 1

Select the area you wish to process and give this a try:

Sub ClearSome()
    Dim r As Range, rr As Range, rClear As Range
    Set rr = Intersect(Selection, ActiveSheet.UsedRange)
    Set rClear = Nothing
    For Each r In rr
        If IsNumeric(r) Then
            If r.Value > 1 Then
                If rClear Is Nothing Then
                    Set rClear = r
                Else
                    Set rClear = Union(rClear, r)
                End If
            End If
        End If
    Next r

    If Not rClear Is Nothing Then
        rClear.Clear
    End If
End Sub

Answer 2

Given the size of your data it might be more efficient to read into an array and loop over the array and then write the array back to the worksheet.

Try this:

Sub RemoveValues()
    Dim values(), arrayWidth As Integer, arrayHeight As Integer, i As Integer, j As Integer

    values = Range("A1:C5") // update as per your set up
    arrayWidth = UBound(values, 2)
    arrayHeight = UBound(values, 1)

    For j = 1 To arrayHeight
        For i = 1 To arrayWidth
            If values(j, i) > 1 Then
                values(j, i) = vbNullString
            End If
        Next i
    Next j

    Range("A1").Resize(arrayHeight, arrayWidth) = values
End Sub