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

AJL82

New Member
Joined
Sep 8, 2014
Messages
5
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Kaper

Board Regular
Joined
Mar 14, 2014
Messages
230
How about using (auto)filter?
select the column
filter
use numerical filter >1000 to show only them
now delete visible contents
remove autofiltering.
 

AJL82

New Member
Joined
Sep 8, 2014
Messages
5
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

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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.
Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,487
Messages
5,529,162
Members
409,851
Latest member
Ingar
Top