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

 

[Rick Rothstein]

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.

Good point! If the OP could have negative values, then my method (as modified by Tetra201) would then require two lines of code...

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

 

[Tetra201]

Here is a three-liner for values that are hardcoded (i.e., not resulting from formulas):

Sub Test()
    Dim Addr As String
    Addr = Range("D7:N" & Cells(Rows.Count, "A").End(xlUp).Row).Address
    Range(Addr) = Evaluate("IF(" & Addr & "<1,""""," & Addr & ")")
End Sub

 

[Rick Rothstein]

Here is a three-liner for values that are hardcoded (i.e., not resulting from formulas):

I assumed since we were physically clearing the cell, that the cells would only contain constants. If they, in fact, contain formulas, the code I posted will not work either.

Sub Test()
    Dim Addr As String
    Addr = Range("D7:N" & Cells(Rows.Count, "A").End(xlUp).Row).Address
    Range(Addr) = Evaluate("IF(" & Addr & "<1,""""," & Addr & ")")
End Sub

Actually, I consider that a two-liner as the Dim statement is not really an "active" line of code per se (as I understand it, it is only used during the compile process in order to set up memory allocations).

 

[kelly mort]

Here is a three-liner for values that are hardcoded (i.e., not resulting from formulas):

Sub Test()
    Dim Addr As String
    Addr = Range("D7:N" & Cells(Rows.Count, "A").End(xlUp).Row).Address
    Range(Addr) = Evaluate("IF(" & Addr & "<1,""""," & Addr & ")")
End Sub

Okay
So far only this and the first solution worked for me.

And this is super fast.

I have really learned new things with this and I am grateful

 

[Rick Rothstein]

Okay
So far only this and the first solution worked for me.

Do you have formulas or constants in the cells being cleared?

 

[kelly mort]

Do you have formulas or constants in the cells being cleared?

I am using
A vba code to add and round up values to the sheet and when the source data is blank, then it shows the zeroes in the cells. That's what I want to take care of.

 

[Nine Zero]

Okay
So far only this and the first solution worked for me.

And this is super fast.

I have really learned new things with this and I am grateful

unfortunately my solution begins to slow down the more data that it has due to it searching every single cell in the loop.... the one stated by @Tetra201 is much faster regardless of size

 

[still learning]

Hi
Would I me wrong to suggest that kelly mort just add a row next to N7 or out of site and use the formula

=If(n7=<1,"",+N7)

and copy it down to the last line. If he is going to add more lines copy the formula down past the last line. it should return a blank cell

mike (still learning with this site's help)
This is the first time i post a reply to a question other than mine!

 

[still learning]

Hi
Would I me wrong to suggest that kelly mort just add a row next to N7 or out of site and use the formula

=If(n7=<1,"",+N7)

and copy it down to the last line. If he is going to add more lines copy the formula down past the last line. it should return a blank cell

mike (still learning with this site's help)
This is the first time i post a reply to a question other than mine!

 

[kelly mort]

Hi
Would I me wrong to suggest that kelly mort just add a row next to N7 or out of site and use the formula

=If(n7=<1,"",+N7)

and copy it down to the last line. If he is going to add more lines copy the formula down past the last line. it should return a blank cell

mike (still learning with this site's help)
This is the first time i post a reply to a question other than mine!

Problem solved already.

I don't wanna use formula for this.

Thanks for your help