Help for marco to autofill in down the row

[Simon Ho]

Hello

I need a marco to help me to autofill in the follow funcition

Before the marco, the sheet is look like this:-

Cell A1: 1-Nov

Cell A2: Blank

Cell A3: Blank

Cell A4: 5-Nov

Cell A5: Blank

Cell A6: Blank

Cell A7: Blank

Cell A8: 12-Dec

Cell A9: Blank

I need the marco to auto fill the content followed by that cell

So the result is

Cell A1: 1-Nov

Cell A2: 1-Nov

Cell A3: 1-Nov

Cell A4: 5-Nov

Cell A5: 5-Nov

Cell A6: 5-Nov

Cell A7: 5-Nov

Cell A8: 12-Dec

Cell A9: 12-Dec

I hope I could explain it clearly.

Thank you.

Simon

[David McRitchie]

Hi Simon Ho ,
see http://members.aol.com/dmcritchie/excel/fillempt.htm

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

Simon Ho <si...@hkg-ro.dhl.com> wrote in message news:383440B5...@hkg-ro.dhl.com...

> I need a marco to help me to autofill in the follow funcition
> Before the marco, the sheet is look like this:-
> Cell A1: 1-Nov
> Cell A2: Blank
> Cell A3: Blank
> Cell A4: 5-Nov
> Cell A5: Blank
>

[Eddie Griffiths]

You dont need a macro.

Select colomn A

F5 Special, goto blanks

in the formala bar add =A1

then press ctrl enter.

Done

Simon Ho wrote in message <383440B5...@hkg-ro.dhl.com>...
>Hello

>
>I need a marco to help me to autofill in the follow funcition
>
>
>Before the marco, the sheet is look like this:-
>
>Cell A1: 1-Nov
>
>Cell A2: Blank
>
>Cell A3: Blank
>
>Cell A4: 5-Nov
>
>Cell A5: Blank
>

>Cell A6: Blank
>
>Cell A7: Blank
>
>Cell A8: 12-Dec
>

>Cell A9: Blank

>
>I need the marco to auto fill the content followed by that cell
>
>So the result is
>
>Cell A1: 1-Nov
>
>Cell A2: 1-Nov
>
>Cell A3: 1-Nov
>
>Cell A4: 5-Nov
>
>Cell A5: 5-Nov
>

[David McRitchie]

Hi Eddie,
I had seen this in John Wakenbach's PC World column
How to Duplicate Values in an Excel List
http://www.pcworld.com/shared/printable_articles/0,1440,9346,00.html
and hadn't paid much attention to it at the time because it involved several
steps. When you indicated select the column rather than a range in the
column, I saw after trying it, I realized the Empty cells selected are in the
A1 to lastcell range. Also I checked to see if it works across multiple
columns and it does. I think the macro solution is a better approach
because it involves less effort on my part, but I should add another macro
using the more efficient approach rather than looping through cells.

There is one thing John includes and you didn't and that is
Reselect the [original] range and choose Edit, Copy.
Then select Edit, Paste Special, choose the Values option, and click OK.
Otherwise you can't really sort, nor delete just any line because the filled
in cells have formula referencing the cell above.

BTW for the rest of us, Ctrl+Enter is a shortcut for
Fill the selected cell range with the current entry
the selected cells being the BLANK cells in the originally selected range.

Thanks,

David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

Eddie Griffiths <edg...@ihug.co.nz> wrote in message news:94290678...@inv.ihug.co.nz...

> You dont need a macro.
>

> Select column A
>
> F5 Special, goto blanks
>
> in the formula bar add =A1

>
> then press ctrl enter.
>
> Done
>
> Simon Ho wrote in message <383440B5...@hkg-ro.dhl.com>...
> >Hello
> >
> >I need a marco to help me to autofill in the follow funcition
> >
> >

> >Before the macro, the sheet is look like this:-

> >
> >Cell A1: 1-Nov
> >
> >Cell A2: Blank
> >
> >Cell A3: Blank
> >
> >Cell A4: 5-Nov
> >
> >Cell A5: Blank
> >
> >Cell A6: Blank
> >
> >Cell A7: Blank
> >
> >Cell A8: 12-Dec
> >
> >Cell A9: Blank
> >

> >I need the macro to auto fill the content followed by that cell

[Eddie Griffiths]

Thanks for that David.

The first time I reply to the group and I get corrected. Don't get me wrong
I appreciate it,

You are right about doing a copy paste special values. I have been caught
out by that.

Sorry to the original poster for not mentioning that important part.

To the MVP's
At work, every one considers me an MVP for Excel. I usually have the answers
to most of their questions and if I dont I can get them from here in under
12 hours usually. Living in New Zealand I can post when I goto bed and the
answer is there when I get up. Excellent service..

I will still reply to the group and show I do know Excel, although not as
well as you guys, but I'm getting there.
Corrections will be highly valued if there are better ways of doing things.
David McRitchie wrote in message <#hQHzTdM$GA.226@cpmsnbbsa02>...