Hello out the

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
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Sojo" wrote:

Hello out the

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.

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.
--
Thanks,
Shane Devenshire


"Sheeloo" wrote:

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
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Sojo" wrote:

Hello out the

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.

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

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

ShaneDevenshire wrote:

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.
--
Thanks,
Shane Devenshire

"Sheeloo" wrote:

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
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Sojo" wrote:

Hello out the

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.

--

Dave Peterson

That worked perfectly!!! Thank you all so much :-)

"Dave Peterson" wrote:

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

ShaneDevenshire wrote:

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.
--
Thanks,
Shane Devenshire

"Sheeloo" wrote:

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
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Sojo" wrote:

Hello out the

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.

--

Dave Peterson

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

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 wrote:

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

Thanks for your thoughts, Dave