Macro clear cells if meet 1 condition

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.
 
S

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
 
S

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.
 
D

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
 
M

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top