Autofill cells in 1 column :code ??

[Luc]

Hello,


I want to do the following :

I have 1 column

Let's say the value in cell A1 is "APPLE"
Let's say the value in cell A15 is "PEACH"
Let's say the value in cell A19 is "BANANA"

The cells between these cells are empty.

Example :

A1 APPLE
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
A13
A14
A15 PEACH
A16
A17
A18
A19 BANANA
A20
.......

Now i want that the cells A2 to A14 contain the value of cell A1, in this
case "APPLE"
I want that the value in cells A16 to A18 contain the value of cell A15, in
this case "PEACH"
I want that the value in cells A19 to A... (the empty cell before the next
non empty cell in this column) contain the value of cell A19, in this case
"BANANA"

The result should be this :

A1 APPLE
A2 APPLE
A3 APPLE
A4 APPLE
A5 APPLE
A6 APPLE
A7 APPLE
A8 APPLE
A9 APPLE
A10 APPLE
A11 APPLE
A12 APPLE
A13 APPLE
A14 APPLE
A15 PEACH
A16 PEACH
A17 PEACH
A18 PEACH
A19 BANANA
A20 BANANA

Of course the row numbers int this example are fictional, they could be any
value.....

Can someone provide me with sompe code to do this.


Thanxxxxx

Luc

 

[Dave Peterson]

Debra Dalgleish shares some techniques:
http://contextures.com/xlDataEntry02.html

 

[dkinn]

you didn't say how far down you wanted to go (all the way to 65,000 rows or
1,000,000 rows?) so here is something you can set limts on

Sub test()
'1 can be changed to any starting row number
'500 can be changed to any ending row number
For cnt = 1 To 500
If IsEmpty(Cells(cnt + 1, 1)) Then
Cells(cnt + 1, 1).Value = Cells(cnt, 1)
End If

Next cnt

End Sub

hope this gives you a starting point

David