Macro to copy data down a column

  • Thread starter Thread starter Neddy
  • Start date Start date
N

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
 
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 said:
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.
Click to expand...

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