How to use a Macro To Clear Cells In Range With Condition For Each Row

How to use a macro to clear cells in range with condition for each row

Requirement:

The user has a range of cells, for example A1 to D5 with currency values in them.

Columns E & F contain the maximum and minimum values permitted for that row.

So for example A1:D1 has values £5, £10, £15, £20.

E1 has the max at £15, and column F has the min at £10.

For the range selected the user wants the macro to delete any values below the minimum or above the maximum for each row.

The user has an 800 row spreadsheet with 40 columns. I've highlighted with conditional formatting all the values to be removed but it's going to take an age.

Solution:

Code:

Sub ClearCells()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range, x As Long
    For x = 1 To LastRow
        For Each rng In Range("A" & x & ":D" & x)
            If rng.Value < Cells(x, 6) Or rng.Value > Cells(x, 5) Then
                rng.ClearContents
            End If
        Next rng
    Next x
    Application.ScreenUpdating = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.