Emptying (cell's) with a numeric value above/below a defined max in a selected field/colum/row

[AJL82]

Hi!

I'm using Excel 2010. If select a column and I wish to delete all numeric values over 1000 in that column how do I do it? I do not want to delete the cells but just Empty them. Can the same method be aplied to drag selection area or if I choose areas from different parts of my worksheet?

Thank you in advance

 

[Kaper]

How about using (auto)filter?
select the column
filter
use numerical filter >1000 to show only them
now delete visible contents
remove autofiltering.

 

[AJL82]

Usefull and helps! Thanks Kaper. But this method can only be aplied to one column at a time if I understand it correctly. Is there a way to filter multiple colums or an entire selected field with a single action?

 

[dermie_72]

the below code will go through each cell in the area you select, and if the value is greater than 1000 it will clear the contents.
You can adjust the value to suit you needs. Select the area and run the macro.

Sub AJL82()
'for http://www.mrexcel.com/forum/excel-...low-defined-max-selected-field-colum-row.html
Dim x As Range
Set x = Selection
For Each cell In x
    If cell.Value > 1000 Then
        cell.ClearContents
        Else
    End If
Next
End Sub

 

[AJL82]

Works beautifully. Thanks dermie_72!