Macro clear cells if meet 1 condition

[Sojo]

Hello out there:

I have a worksheet (sheet 1) I need to write a macro that will look at
A4:A2500, clear contents of all cells that have a value <140, then look at
column C4:C2500 and clear contents of all cells that have a value >30. I
have more conditions, but can modify the code to meet the requirements.

Would appreciate any assistance on this.

 

[Sheeloo]

Try
'This will work on the current sheet...
Sub clear()
Range("A4:A2000").Select
For Each CurCell In Selection
If CurCell.Value < 140 Then CurCell.ClearContents
Next CurCell
End Sub

 

[ShaneDevenshire]

Hi,

Some slightly shorter code:

Sub clear()
For Each cell In Range("A4:A2000").Select
If cell < 140 Then cell.ClearContents
Next cell
End Sub

By the way if this proves slow, there is a faster way to do it. The code is
longer but the execution time is shorter. Maybe as much as 50-100 times
faster. Let us know.

 

[Max]

Shane

.. there is a faster way to do it ..

I'd would like to learn this. If you could ..
Thanks
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---

 

[Dave Peterson]

You left the .select in the code.

I bet you wanted:

Sub clear()
For Each cell In Range("A4:A2000")
If cell < 140 Then cell.ClearContents
Next cell
End Sub

 

[Sojo]

That worked perfectly!!! Thank you all so much

 

[Max]

Dave,
I'm not sure if Shane is coming back to this thread

He mentioned earlier:

.. there is a faster way to do it. The code is longer but the execution
time is shorter.
Maybe as much as 50-100 times faster

I'd be interested to learn more about that alternative
If you could ..

Muchas gracias`
Max

 

[Dave Peterson]

I'm not sure what Shane meant.

Maybe he was going to use .specialcells to limit the number of cells to just the
numeric constants???

Dim myRng as range
dim myCell as range
set myrng = nothing
on error resume next
set myrng = somerange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
on error goto 0

if myrng is nothing then
msgbox "No numbers"
else
for each mycell in myrng.cells
...
next mycell
end if

But that's just a guess.

 

[Max]

Thanks for your thoughts, Dave