Sub FillBlanks()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, ar As Range
Set rng = Cells(Rows.Count, 2).End(xlUp)(31)
rng.Offset(1, 0) = "dum"
Set rng2 = Range(Range("B1"), rng.Offset(1, 0))
Set rng1 = rng2.SpecialCells(xlBlanks)
rng1.Formula = "=" & rng1(1) _
.Offset(-1, 0).Address(0, 0)
For Each ar In rng1.Areas
ar.Value = ar.Value
Next
rng.Offset(1, 0).Delete shift:=xlShiftUp
End Sub

worked for me.

--
Regards,
Tom Ogilvy



"Neddy" wrote:


Hello,

Say col B has data at various random positions down a column. Starting
on a row that contains data, I want to copy that data down the column
until it strikes new data, then copy that new data down the column
until it strikes a change, and copy that down etc. The way out of the
loop would be that data will always be encountered within 30 rows.

I do this every day, so an automated process would help

Col B................Col B
Example..........Require
13.14.............13.14
blank..............13.14
blank..............13.14
blan...............13.14
blan...............13.14
13.50..............13.50
blank...............13.50
blank...............13.50
14.02...............14.02
blank................14.02
blank.....................etc,etc,etc
blank
blank
blanl
15.00
blank
blank
blank
End loop after 30 blanks

Thanks
Neddy


--
Neddy
------------------------------------------------------------------------
Neddy's Profile: http://www.excelforum.com/member.php...o&userid=24345
View this thread: http://www.excelforum.com/showthread...hreadid=525405