macro to autofill data in the same column

[ockyoz]

Hi guys, often I have data like the following:

rose





Lilly





tulip

and I need to autofill the cells in the column (got thousands of names and the position of the names is always different) so I have been double clicking at the lower right corner of the cell every time but it's time consuming. Could a macro help me out??
Cheers

 

[footoo]

Assuming "rose" is in A2 :
- Select from A2 to the last cell to be filled.
- Go to SpecialCells/Blanks
- Type =A2
- Press Ctrl+Enter
- Select column A and Copy/PasteSpecial-Values

 

[Akuini]

Hi guys, often I have data like the following:

rose





Lilly





tulip

and I need to autofill the cells in the column (got thousands of names and the position of the names is always different) so I have been double clicking at the lower right corner of the cell every time but it's time consuming. Could a macro help me out??
Cheers

Here's another way, using macro:

Sub a1009435()

Dim vr
Dim i As Long
Dim rr As Long

rr = Range("A" & Rows.count).End(xlUp).row
vr = Range("A1:A" & rr)

For i = 2 To rr
     If vr(i, 1) = vbNullString Then vr(i, 1) = vr(i - 1, 1)
Next i
  
Range("A1").Resize(rr, 1).Value = vr
  
End Sub

 

[footoo]

Here's another way, using macro:

Sub a1009435()

Dim vr
Dim i As Long
Dim rr As Long

rr = Range("A" & Rows.count).End(xlUp).row
vr = Range("A1:A" & rr)

For i = 2 To rr
     If vr(i, 1) = vbNullString Then vr(i, 1) = vr(i - 1, 1)
Next i
  
Range("A1").Resize(rr, 1).Value = vr
  
End Sub

Or you could use the macro recorder to record the steps in post #2.

 

[ockyoz]

Cheers, it works (kinda) but it stops at the third word...

 

[ockyoz]

Have tried recording a macro but it never works the next time as the position of the words always changes...

 

[My Aswer Is This]

This script worked for me. If you only have three words it's going to stop after the second word. And if there are only three values it's only continues if there are more words below the third word.

Cheers, it works (kinda) but it stops at the third word...

 

[Akuini]

Cheers, it works (kinda) but it stops at the third word...

You mean it stops at the last word, right?
Well, for the last word we don't know how many blank cell below it you want to fill.
So you have to do it manually.

 

[footoo]

Have tried recording a macro but it never works the next time as the position of the words always changes...

Record the macro, post the code and you will learn how to tweak the macro so that it works on any number of rows.

 

[footoo]

You mean it stops at the last word, right?
Well, for the last word we don't know how many blank cell below it you want to fill.
So you have to do it manually.

Unless some other column can be used to find the last row.