How to use a macro to clear cells in range with condition for each row
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to add a password to a macro |
| How to use a macro to auto-scroll |
| How to loop a macro with various length columns |
| How to use Excel VBA macro to import data from multiple workbooks to main workbook |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.