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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Kaper

Board Regular
Joined
Mar 14, 2014
Messages
235
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,441
Members
430,548
Latest member
hh_dh2001

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
Top