moving data dynamically based on empty cell

[stickandrock]

I have a data sheet that looks like this...

col a col b
abc 10
20
45
efg 5
18
10

I need it to look like this...
col a col b
abc 10
abc 20
abc 45
efg 5
efg 18
efg 10

since there is no consistency to the number of rows in col b with data, i
have to come up with a formula that is dynamic

The file has 20,000 rows so doing it manually is not the way to go.

Thanks

 

[Rick Rothstein]

Give this macro a try...

Sub FillInTheBlanks()
Dim Area As Range, LastRow As Long
Const ColLetter As String = "A"
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
For Each Area In Columns(ColLetter)(1).Resize(LastRow). _
SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub