Excel Copy Down Routine

[purceld2]

I have a task that I have to perform manually on a regular basis, which involves having to copy the data from the above cells to the empty cells below until a new value is in the column.

Below is an example of a before and after would somebody have a routine which could do this automatically.

Thanks in Advance

Excel Workbook
	A
1	Test 1
2
3
4
5
6
7
8
9	Test 2
10
11	Test 3
12
13
14
15
16
17
18	Test 4
Sheet1

After

Excel Workbook
	A
1	Test 1
2	Test 1
3	Test 1
4	Test 1
5	Test 1
6	Test 1
7	Test 1
8	Test 1
9	Test 2
10	Test 2
11	Test 3
12	Test 3
13	Test 3
14	Test 3
15	Test 3
16	Test 3
17	Test 3
18	Test 4
Sheet1

 

[Weaver]

try the following bit of code:

Sub infill()
    On Error GoTo noGaps
    With Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]"
        .Value = .Value
    End With
noGaps:
End Sub

 

[VoG]

Try

Sub FillDown()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("A1:A" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[purceld2]

Thanks you very much