Quick macro for copying down in a variable range

[cyse]

Hi, I'm hoping you can help.

I have a long list of cells that I need to fill in the blanks between entries with the contents of the last cell...so if the data looked like this...

Apple


Pear

Orange

I want it to look like this...


Apple
Apple
Apple
Pear
Pear
Orange

I thought the best way to do it was to use a macro with relative references and just repeatedly call the macro...here is what I've created

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/22/2007 by Danette Nguyen
'
' Keyboard Shortcut: Ctrl+q
'
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A9").Select
Selection.FillDown
Selection.End(xlDown).Select
End Sub

...but the cell range A1:A9 is of defined length and my length between each entry is variable. How can I modify this macro to achieve what I want. Optimally, the macro replaces me from the following keystrokes...

starting at the first entry
ctrl-shift-down arrow
shift-up arrow
ctrl-d
ctrl-shift-down arrow
end

Thanks for your help

 

[Norie]

Do you really need code?

1. Select the column with the blanks.
2. Goto Edit>Goto...Special and select blanks.
3. Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
4. Select the column, copy and paste special values.

 

[cyse]

I need to fill in the blanks because the rest of the columns have data and I am trying to fill in those cells so I can use the information as a database. Without those cells filled in, I won't be able to accomplish creating the database of information.
Thanks

 

[eblake]

There is probably a better way to do it by looping through the blanks and using fill down, but I already had something similiar written...

Sub Filler()
myVar = "OOPS"
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For I = 1 To LastRow
    If Range("A" & I).Value > "" Then
        myVar = Range("A" & I).Value
    Else
        Range("A" & I).Value = myVar
    End If
Next I
End Sub

 

[Norie]

I need to fill in the blanks because the rest of the columns have data and I am trying to fill in those cells so I can use the information as a database. Without those cells filled in, I won't be able to accomplish creating the database of information.
Thanks

I realise what you want to do, but do you really need to use code.

Note I'm not saying it's not possible with code.