VBA to fill cells with values

[afrobea_r]

Dear Excel community,

Currently I have a column that looks like this,
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Mango[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Apple[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Orange[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Pear[/TD]
[/TR]
</tbody>[/TABLE]


Is there a VBA that can help populate the blank cells above to become the following:
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Mango[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apple[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apple[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apple[/TD]
[/TR]
[TR]
[TD="class: xl65"]Orange[/TD]
[/TR]
[TR]
[TD="class: xl65"]Orange[/TD]
[/TR]
[TR]
[TD="class: xl65"]Orange[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pear[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pear[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pear



[/TD]
[/TR]
</tbody>[/TABLE]


My existing formula is as such but it only will fill if I select the column up till the row I want
On Error Resume Next
With Selection.SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "=R[+1]C"
.Value = .Value
End With
End Sub

Help greatly appreciated!! Thank you!!

 

[Michael M]

Try either

   With Range("A:A").SpecialCells(xlCellTypeBlanks)
      .FormulaR1C1 = "=R[-1]C"
      .Value = .Value
    End With

OR for a specific range

    With Range("A[color=red]1[/color]:A[color=red]100[/color]").SpecialCells(xlCellTypeBlanks)
      .FormulaR1C1 = "=R[-1]C"
      .Value = .Value
    End With

 

[afrobea_r]

Hi Michael thanks for your help, but the code only populates the rest of the cells based on the the first row cell value, and not according to the different values that are present in the column. Is there a work around?

 

[Fluff]

How about

Sub FillBlanks()
   With Range("A1:A100")
      On Error Resume Next
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      On Error GoTo 0
      .Value = .Value
   End With
End Sub

 

[Jogender singh]

You need to try this it will definitely work

With Range("A:A").SpecialCells(xlCellTypeBlanks)
On error resume next
.FormulaR1C1 = "=R[-1]C"
.Value =cells("=R[-1]C").value
End With

 

[Fluff]

Not sure if you are aware, but you are replying to a thread that is almost 2 years old.
Also you solution will leave the formula in the cells, rather than converting it to a value.

 

[Jogender singh]

I am not professional
So i am not sure
But you need to use it for once
Thank you

 

[Fluff]

I don't need to try it. I can tell just by looking at the code that it will leave the formula in the cells.