Macro to populate a range of blank cells from a base cell

Hi,
I have a requirement to populate a range of cell from the value of a base cell.
Scenario.
1. Base cell = B1.
2. There is another value in cell B7 and cells B2:B6 are blank.
3. I want to copy the value of B1 to B2:B6.
4. There is another value in cell B10 and cell B8:B9 are blank.
5. I want to copy the value of B7 to B8:B9.

Based on the scenario above, I want a macro which copy the values to a dynamic range of cells.

From recording a macro, I get the following code:-
--------------------------------------------------------------------------------------------------------------------------------------------
Sub Flowdown()
'
' Flowdown Macro
'
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A6").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlDown).Select
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------

The problem with the code is the red line above. The selection of cells to paste is not dynamic. How can I change the line to make it dynamic based on the number of blank cells?

Thanks in advance.
Eddie Lee

You can do this without a macro, or:

Please Login or Register  to view this content.