remove zeroes macro

[kylefoley76]

This macro is not work. It is supposed to copy all cells from b to cb, then down four rows and paste the value, then go back and replace all cells which have a zero in them with a blank. instead it will copy all cells from b to cb, copy it, then paste that in the next three rows down.

also if someone could rewrite the macro so that columb B through CB are clearly visible. I don't know why people often name the column after the number they appear in the alphabet, i always have to count which letter it is, very confusing. thanks in advance.

Dim Cell As Range
Dim RngFrom As Range
Dim RngTo As Range

Set RngFrom = Selection
Set RngTo = Cells(RngFrom.Row + 1, 2).Resize(15, 78)

Cells(RngFrom.Row, 2).Resize(, 78).Copy Destination:=RngTo

For Each Cell In RngTo
If Cell.Value = 0 Then
Cell.Value = ""
Else
Cell.Value = Cell.Value
End If
Next Cell

RngTo.Activate

Set Cell = Nothing
Set RngFrom = Nothing
Set RngTo = Nothing
End Sub

 

[kylefoley76]

Thanks for helping me out, but that macro will copy, for example, c1:cb4, then will remove the zeroes and paste them at c5:cb9.

I need it to paste them in c1:cb4

 

[Rick Rothstein]

Thanks for helping me out, but that macro will copy, for example, c1:cb4, then will remove the zeroes and paste them at c5:cb9.

I need it to paste them in c1:cb4

Okay, I'm willing to try again. What about this code (note, it is converting any formulas in the 4x78 cell range to values - your first post seemed to indicate you wanted values when completed)...

Sub ChangeToValuesAndRemoveZeroes()
  With ActiveCell.Resize(4, 78)
    .Value = .Value
    .Replace 0, "", xlWhole
  End With
End Sub

 

[kylefoley76]

ok, thanks that did it. i really appreciate that work you put into this.