Macro to copy data down a column

[Neddy]

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

 

[Guest]

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.

 

[Neddy]

Thanks Tom, much appreciated.

Nedd

 

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

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