Paste cell above if blank

[carlosjcabral]

New at this, not sure what I did wrong. All I want it to do is look at the cell and if it is blank then paste the value in the cell above it. Then reapeat this until the end of the page.


dim rz As Long, z As Long

rz = Cells(Rows.Count, 4).End(xlUp).Row
For z = rz To 1 Step -1

If Cells(z, 2).Value = "" Then Value = Cells(z - 1, 2).Value

Next z

 

[kpark91]

dim rz As Long, z As Long

rz = Cells(Rows.Count, 4).End(xlUp).Row
For z = rz To 1 Step -1

If Cells(z, 2).Value = "" Then Cells(z, 2).Value = Cells(z - 1, 2).Value

Next z

<!-- / message -->

 

[Sektor]

kpark91, You'll get error if B1 is blank.

 

[kpark91]

Ahh.. I didn't notice that.
Thanks Sektor.

dim rz As Long, z As Long

rz = Cells(Rows.Count, 4).End(xlUp).Row
For z = rz To 2 Step -1

If Cells(z, 2).Value = "" Then Cells(z, 2).Value = Cells(z - 1, 2).Value

Next z
<!-- / message -->

 

[carlosjcabral]

That just what I was going to post it say runtime error 1004 application defined or object error when I do that.

 

[Sektor]

Are you sure that you define last row by D column?

 

[carlosjcabral]

I changed the 1 to a 2 and I stop getting the error, but its looks like it only copys and paste once for each value. I want it to do each row on the sheet like the first column. Thanks for your help on this.


40012 40012
40012 40012
40012
40012
40013 40013
40013 40013
40013
40013


dim rz As Long, z As Long

rz = Cells(Rows.Count, 4).End(xlUp).Row
For z = rz To <b>2</b> Step -1

If Cells(z, 2).Value = "" Then Cells(z, 2).Value = Cells(z - 1, 2).Value

Next z

 

[Peter_SSs]

but its looks like it only copys and paste once for each value. I want it to do each row on the sheet like the first column.

Can you explain in more detail what you mean by that? Do you mean each column?

For column D, see if this does what you want. It doesn't require checking every cell one at a time. Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> FillBlanks()<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks)<br>        .FormulaR1C1 = "=R[-1]C"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[carlosjcabral]

I hope this helps my excel sheet looks like the on on the right with all the blanks. I want it to go down the sheet and if the cell is blank then paste the cell above it. like the left side example. One thing to note... the data is in column B to not D so I sure I defined it incorrectly. I hope the helps. Thanks.



11 11
11
11
11
11
33 33
33
33
33
33
33
22 22
22
22
22
22
22
44 44
44
44
44
44
44
end of page

 

[kpark91]

How do you identify whether it's the end of page?

dim rz As Long, z As Long

rz = Cells(Rows.Count, 2).End(xlUp).Row
For z = 2 To rz
    If Cells(z, 2).Value = "" Then Cells(z, 2).Value = Cells(z - 1, 2).Value
Next z