Copy filled cells in a column to empty cells below - until the next filled cell occures - and so on

[TROWKRO]

Hi. I have a table with a column A containing filled cells and empty cells. I would like to create a VBA that copies the filled cells to the empty cells below, and when a new cell value occures, this will be copied to the next cells below etc. until there are no more values in column B. Would this be possible?

Thanks at advance.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "10"[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "25"[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "25"[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "25"[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "25"[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "30"[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "30"[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "30"[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "30"[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "30"[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]empty - should be filled with "30"[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Sub Trowkro()
   With Range("A2", Range("B" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

 

[TROWKRO]

Great, It works perfect. Thanks a lot

 

[Fluff]

You're welcome & thanks for the feedback

 

[TROWKRO]

Hi again. Effectively, there is a little problem. The column B should not be filled the same way as with the column A. Column B should not be touched at all.
The proposed code fills the column B the same way as the column A if there is an empty cell or more. Could we tweak the code a little to avoid this?

 

[Fluff]

Oops I missed a bit out, it should be

Sub Trowkro()
   With Range("A2", Range("B" & Rows.Count).End(xlUp)[COLOR=#ff0000].Offset(, -1)[/COLOR])
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

 

[TROWKRO]

Now it is spot on thanks again.

 

[Fluff]

You're welcome