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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
How about using (auto)filter?
select the column
filter
use numerical filter >1000 to show only them
now delete visible contents
remove autofiltering.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,165
Messages
6,170,468
Members
452,329
Latest member
Irefsports

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top