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]

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.

If you show us the code that is doing the above, I think we should be able to modify it for you to not output the zeros so that there would be no need to run the extra code to remove them.

By the way, you apparently misled us in you original message... you said you wanted to blank out values less than 1 (which would suggest you had decimal values like 0.45, 0.82, etc.) whereas you actually want to remove cells with 0 in them. I would think this code would do that...

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

 

[kelly mort]

Hello this is the code :

Sub Copy_Add()
    Dim Ms As Worksheet, Cs As Worksheet, x As Long, LastRow, sht As String
    Set Ms = Sheet3
    
    term = Sheet2.Range("E11").Text
    Set Cs = Sheets(sht)
    
    LastRow = Cs.Range("B1007").End(xlUp).Row
    Application.ScreenUpdating = False
    For x = 7 To LastRow Step 1
    With Ms
    .Range("A" & x).Value = Cs.Range("AB" & x).Value
    .Range("B" & x).Value = Cs.Range("B" & x).Value
    .Range("C" & x).Value = Cs.Range("C" & x).Value
    .Range("D" & x).Value = Application.Sum(Cs.Range("G" & x).Value, Application.RoundUp(Cs.Range("P" & x).Value * 0.5, 0))
    .Range("E" & x).Value = Application.Sum(Cs.Range("H" & x).Value, Application.RoundUp(Cs.Range("Q" & x).Value * 0.5, 0))
    .Range("F" & x).Value = Application.Sum(Cs.Range("I" & x).Value, Application.RoundUp(Cs.Range("R" & x).Value * 0.5, 0))
    .Range("G" & x).Value = Application.Sum(Cs.Range("J" & x).Value, Application.RoundUp(Cs.Range("S" & x).Value * 0.5, 0))
    .Range("H" & x).Value = Application.Sum(Cs.Range("K" & x).Value, Application.RoundUp(Cs.Range("T" & x).Value * 0.5, 0))
    .Range("I" & x).Value = Application.Sum(Cs.Range("L" & x).Value, Application.RoundUp(Cs.Range("U" & x).Value * 0.5, 0))
    .Range("J" & x).Value = Application.Sum(Cs.Range("M" & x).Value, Application.RoundUp(Cs.Range("V" & x).Value * 0.5, 0))
    .Range("K" & x).Value = Application.Sum(Cs.Range("N" & x).Value, Application.RoundUp(Cs.Range("W" & x).Value * 0.5, 0))
    .Range("L" & x).Value = Application.Sum(Cs.Range("O" & x).Value, Application.RoundUp(Cs.Range("X" & x).Value * 0.5, 0))
    .Range("M" & x).Value = Cs.Range("Y" & x).Value
    .Range("N" & x).Value = Application.Sum(Ms.Range("D" & x & ":M" & x).Value)
    '.Range("O" & x).Value = Application.CountIf(Ms.Range("D" & x & ":M" & x), "> 0")
    End With
    Next x
End Sub

 

[kelly mort]

By the way, you apparently misled us in you original message... you said you wanted to blank out values less than 1 (which would suggest you had decimal values like 0.45, 0.82, etc.) whereas you actually want to remove cells with 0 in them. I would think this code would do that...

Hello I have posted the code for some days now.

Is there a way to fix it as you suggested?

A way to make it better and faster