Macro to copy data down a column

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

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
>
>

Originally Posted by Tom Ogilvy

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