Delete any cells with values greater than specific cell value

Question

I want to create a macro for the following:

For each row, if there are cell values in range C3:ACP3 that are >= value of ACU3, I want to replace that cell value with blank. I want to do this for every row, and each time the macro should reference the value in the ACU column for that row.

Answer 1

Try this:

Sub makeBlank()

Dim r As Range
Set r = Excel.ThisWorkbook.Sheets("Sheet1").Range("C3:ACP3")

Dim v As Double
v = Excel.ThisWorkbook.Sheets("Sheet1").Range("ACU3").Value

Dim c
For Each c In r
    If c.Value >= v Then
        c.Value = ""
    End If
Next c

End Sub

---

EDIT

I suspect this will be quicker using arrays:

Sub makeBlank2()

Dim v
v = Excel.ThisWorkbook.Sheets("Sheet1").Range("ACU3").Value

Dim Arr() As Variant
Arr = Sheet1.Range("C3:ACP3")

Dim R, C As Long
For R = 1 To UBound(Arr, 1)
    For C = 1 To UBound(Arr, 2)
        If Arr(R, C) > v Then
            Arr(R, C) = ""
        End If
    Next C
Next R

Sheet1.Range("C3:ACP3") = Arr

End Sub

Answer 2

Try this:

Sub FindDelete()

Dim ACU_Val As Double
Dim cl As Range
Dim rw As Long

    For rw = 1 To Rows.Count
        If Range("ACU" & rw).Value = "" Then Exit For

        ACU_Val = Range("ACU" & rw).Value

        For Each cl In Range("C" & rw & ":ACP" & rw)
            If cl.Value >= ACU_Val Then cl.Value = ""
        Next cl
    Next

End Sub

Answer 3

you need to iterate over your desired range of cells and for each cell which contents are above the threshold value in the ACU column of the same row just clear its contents.

For Each c In Range("C3:ACP3")
  If c.Value >= Cells(c.Row, "ACU") Then
    c.clearContents
  End If
Next c

Answer 4

Simple :

Dim myCell As Range
numberOfRows = 1000

For i = 0 To numberOfRows 
    Dim myRow As Range

    Set myRow = [C3:ACP3].Offset(i, 0)
    bound = Intersect([acu3].EntireColumn, myRow.EntireRow) 

    For Each myCell In myRow
        If myCell >= bound Then myCell = ""
    Next
Next