Macro clear cells if meet 1 condition

  • Thread starter Thread starter Sojo
  • Start date Start date
S

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.
 
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
 
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.
 
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
 
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
Click to expand...

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

Muchas gracias`
Max
 
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.
 
Back
Top