moving data dynamically based on empty cell
 

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

moving data dynamically based on empty cell
 

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

--
Rick (MVP - Excel)


"stickandrock" wrote in message
...
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