Hello,

Say col B has data at various random positions down a column. Startin
on a row that contains data, I want to copy that data down the colum
until it strikes new data, then copy that new data down the colum
until it strikes a change, and copy that down etc. The way out of th
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
Nedd

--
Nedd
-----------------------------------------------------------------------
Neddy's Profile: http://www.excelforum.com/member.php...fo&userid=2434
View this thread: http://www.excelforum.com/showthread.php?threadid=52540

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

Thanks Tom, much appreciated.

Nedd

--
Nedd
-----------------------------------------------------------------------
Neddy's Profile: http://www.excelforum.com/member.php...fo&userid=2434
View this thread: http://www.excelforum.com/showthread.php?threadid=52540

Tom Ogilvy Wrote:
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



Tom,

Sorry, that doesn't work. What I got was a series of 30 "=" signs then
"dum"", a further series of 30 "="" signs, then dum etc etc, it fails
after 60 equal signs with the debugger highlighting these two lines.
The = signs start below data on the column to the left of the required
column.


rng1.Formula = "=" & rng1(1) _
Offset(-1, 0).Address(0, 0)

Any clues?

Thanks again,
Neddy


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