Clear cell Contents if condition is met

[kelly mort]

I need a script to clear contents of cells in a range that may contain values less than 1.

The data starts from A7 and I am clearing from the range D7 to N7 down to the last used row.

Thanks
Kelly

 

[Nine Zero]

try this out

Sub ClearLessThanOne()
Dim lastRow As Integer
Dim r As Range
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For Each r In Range("D7:N" & lastRow)
    If r.Value < 1 Then
        r.Value = ""
    End If
Next

End Sub

 

[njimack]

Sub Clear_Contents()

Dim c As Range


Application.ScreenUpdating = False


For Each c In Range(Cells(7, 1), Cells(Rows.Count, 1).End(xlUp))
    If c.Value < 1 Then Range(Cells(c.Row, 4), Cells(c.Row, 14)).ClearContents
Next c


Application.ScreenUpdating = True


End Sub

 

[kelly mort]

try this out

Sub ClearLessThanOne()
Dim lastRow As Integer
Dim r As Range
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For Each r In Range("D7:N" & lastRow)
    If r.Value < 1 Then
        r.Value = ""
    End If
Next

End Sub

Great!!!

Working just cool

Thanks

 

[kelly mort]

Sub Clear_Contents()

Dim c As Range


Application.ScreenUpdating = False


For Each c In Range(Cells(7, 1), Cells(Rows.Count, 1).End(xlUp))
    If c.Value < 1 Then Range(Cells(c.Row, 4), Cells(c.Row, 14)).ClearContents
Next c


Application.ScreenUpdating = True


End Sub

Nothing happens
I have used Sheet1.Range(

 

[Rick Rothstein]

I would think this one-liner should work for you...

[table="width: 500"]
[tr]
	[td]Sub ClearLessThanOne()
  Range("A7", Cells(Rows.Count, "A").End(xlUp)).Replace "0.*", "", xlWhole, , , , False, False
End Sub[/td]
[/tr]
[/table]

 

[kelly mort]

I would think this one-liner should work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ClearLessThanOne()
  Range("A7", Cells(Rows.Count, "A").End(xlUp)).Replace "0.*", "", xlWhole, , , , False, False
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

It did not clear the cells. I just don't know why

 

[Tetra201]

Rick,

I think it should be

Sub ClearLessThanOne()
  Range("[COLOR=#FF0000]D7:N[/COLOR]" & Cells(Rows.Count, "A").End(xlUp).Row).Replace "0.*", "", xlWhole, , , , False, False
End Sub

 

[Rick Rothstein]

Rick,

I think it should be

Sub ClearLessThanOne()
  Range("[COLOR=#FF0000]D7:N[/COLOR]" & Cells(Rows.Count, "A").End(xlUp).Row).Replace "0.*", "", xlWhole, , , , False, False
End Sub

Yes, you are correct... thanks for catching that. As it turns out, I simply did not read the original question thoroughly enough.

 

[JoeMo]

The OP states clear values less than 1. The OP didn't say that all values were greater than zero. Rick's one-liner will not clear negative values.