Seeking method to replace zeros with whatever number is populated in cell above

[auto.pilot]

I searched and also tried to solve this problem but I'm stumped.

I have several columns populated with positive numbers, but some are zero. I'd like a VBA method to change the zero's to whatever number is in the cell directly above. I suppose this needs to be some sort of a loop, since a column may have several numbers, then more than one cell with zero, then more positive numbers. There are about 50 columns, and 30 rows of data. This would be quite simple with a formula, but I'd like to do it with VBA. How can I do this?

One column looks something like this. As an example, the first and second instance of zero should be changed to 16.4 & 16.4, then the zeros at the bottom should be changed to 12.2 & 12.2

16.4

0

0

12.2

0

0

17.85

Thanks in advance.

Jim

 

[Rick Rothstein]

It would help if you told us the columns involved or a way to identify them.

 

[auto.pilot]

It would help if you told us the columns involved or a way to identify them.

My headers are in row 7, data starts in row 8. The columns are AC:AS then also AV:BG

Thanks

jim

 

[auto.pilot]

I searched and also tried to solve this problem but I'm stumped.

I have several columns populated with positive numbers, but some are zero. I'd like a VBA method to change the zero's to whatever number is in the cell directly above. I suppose this needs to be some sort of a loop, since a column may have several numbers, then more than one cell with zero, then more positive numbers. There are about 50 columns, and 30 rows of data. This would be quite simple with a formula, but I'd like to do it with VBA. How can I do this?

One column looks something like this. As an example, the first and second instance of zero should be changed to 16.4 & 16.4, then the zeros at the bottom should be changed to 12.2 & 12.2

16.4

0

0

12.2

0

0

17.85

Thanks in advance.

Jim

I found this, which almost works... Zeros are correctly replaced with the value of the cell above, but it fails when the column has more than two zeros next to each other.
Is it possible to adjust this code, or do I need a different solution?

Sub ReplaceZeros()
Application.Calculation = xlCalculationManual
Dim cell As Range
Set cell = Cells(Rows.Count, "A").End(xlUp)

While cell.Row > 1

If cell = 0 Then
    cell = cell(0)
End If

Set cell = cell(0)

Wend

Application.Calculation = xlCalculationAutomatic

End Sub

 

[Rick Rothstein]

See if this macro does what you want...

Sub ReplaceZerosWithValueAbove()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  For Each Ar In Range("AC7:AS" & LastRow & "," & "AV8:BG" & LastRow).Areas
    Ar.Replace 0, "#N/A", xlWhole, , , , False, False
    Ar.SpecialCells(xlConstants, xlErrors).FormulaR1C1 = "=R[-1]C"
    Ar.Value = Ar.Value
  Next
End Sub

 

[auto.pilot]

Excellent solution. Thanks for sharing your time and knowledge!