Formula or macro to retrieve cell above to replace zero

[GreyFox8991]

Greetings excel community,

I have some cells that contain a zero and I would like to replace the zero value with the value from the preceding row (the cell above). I saw a formula online which is as follows:=INDIRECT(ADDRESS(ROW()-1|COLUMN())). However, I would have to copy and paste this value multiple times in order to replace the zero values in the cells. Would anyone have a suggestion or an addition to the formula that might extrapolate this to other cells? A3 contains the formula i referenced earlier. This formula would have to be pasted into the next cell to replace the zero in order to retrieve the value from the cell above. Any insight would be greatly appreciated.

Book2
	A
2	23435357
3	23435357
4	23471373
5	0
6	0
7	23474577
8	0
9	23471728
10	23476975
11	0
12	23447346
13	23447460
14	0
Sheet1

Cell Formulas
Range	Formula
A3	A3	=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

 

[Skyybot]

Try this on a copy of your Workbook

Public Sub ReplaceZero()
Dim lRow As Long, rng As Range, cell As Range
lRow = Columns(1).Rows.End(xlDown).Row
Set rng = Range("A1:A" & lRow)
For Each cell In rng
    If cell.Value = 0 Then cell.Value = cell.Offset(-1, 0).Value
Next cell
End Sub

 

[GreyFox8991]

Thank you Skybot this is helpful.. Your solution is much appreciated

 

[Peter_SSs]

You could also do this fairly quickly manually as follows

1. Select the range (or whole column)
2. Ctrl+F to invoke the Find dialog
3. Find what: 0 and set 'Match entire cell contents' then click 'Find All'
4. Immediately type Ctrl+A
5. Close the Find dialog
6. With all the 0 cells still selected, Press the = key then the up arrow key then Ctrl+Enter
7. Optional: If you want to replace the formulas with those values select the whole range/column again and do a Copy -> PasteSpecial (Values)