Macro to Copy/paste

[aa2000]

Hi guys

I have an excel sheet where the first column consists of Cells containing values and blank cells. I am trying to get the macro to go through that column (from top to bottom) and if the the cell has a value in it copy that value, then go to the next cell, and if it is blank paste that value, or if it has its own value copy that.

i.e. go from this:
x
blank
blank
blank
y
z
blank

To this:
x
x
x
x
y
z
z

Currently the code I have copies the values with no problem (I think), but I cannot get it to paste it if the cell is blank. Heres my code so far:

Sub CopyPaste()
Set Rng1 = Range("A3:A65536")
Dim a As Object
For Each a In Rng1.Cells
If a.Value = "*" Then a.Value.Copy
If a.Cell = "" Then
Next a
End Sub

Any help is very much appreciated

Thanks!

 

[baitmaster]

I have a piece of code that does this already, I wouldn't copy and paste, there's no need

Sub fillValues()
Dim i As Integer, j As Integer, currentVal As Variant
For j = start_column To end_column
    currentVal = Cells(start_row, j).Value
    For i = start_row + 1 To end_row
        If Cells(i, j).Value = "" Then Cells(i, j).Value = currentVal
        currentVal = Cells(i, j).Value
    Next i
Next j
End Sub

start_row is a function, effectively = selection.row
end_row is another function, =selection.row + selection.rows.count - 1
etc

you can change these to suit yourself

 

[aa2000]

Baitmaster, Thank you for the quick response, but I'm very new to VBA so I didn't understand what you meant when you explained start_row and end_row.
Its starting from A3 and continuing for the whole A column, so how would I add that to you macro?

Thanks

 

[baitmaster]

ok, not a problem, use the code adjusted as follows:

Sub fillValues()
Dim i As long, currentVal

currentVal = Cells(i, 1).Value

For i = 3 To 65536
    If Cells(i, 1).Value = "" Then Cells(i, 1).Value = currentVal
    currentVal = Cells(i, i).Value
Next i

End Sub