Filling in missing data

Try this; I taking you literally that you have all rows filled.

Make copy of data first!

Sub FillABC()
With Worksheets("Sheet1") '<=== change
For r = 2 To 65536 '<== assuming data starts row 2
If .Cells(r, 1) = "" Then
.Cells(r, 1) = .Cells(r - 1, 1)
.Cells(r, 2) = .Cells(r - 1, 2)
.Cells(r, 3) = .Cells(r - 1, 3)
End If
Next r

End With
End Sub

HTH

"filky" wrote:

>
> Hi,
> I have 6 worksheets with 65,536 lines filled in each one. There are
> five columns. The fourth and fifth are filled in but the first three
> are only filled in every 20 or 30 lines (the number is not
> consistent).
> I would like to be able to write a routine or formulae that could fill
> in the empty cells with the last bit of data so I have a full sheet to
> work with. For example,
>
> John 123 456 789 abc
> 523 def
> 566 ghi
> Fred 423 623 789 abc
> 523 def
> 566 ghi
>
> Needs to look like this;
>
> John 123 456 789 abc
> John 123 456 523 def
> John 123 456 566 ghi
> Fred 423 623 789 abc
> Fred 423 623 523 def
> Fred 423 623 566 ghi
>
> Formatting is a little off so I ave attached a screenshot if it helps.
> Any help would be much appreciated as this could be a very long exercise
> otherwise..!
>
> Thanks
>
> Paul.
>
>
> +-------------------------------------------------------------------+
> |Filename: Excelforum#1.jpg |
> |Download: http://www.excelforum.com/attachment.php?postid=4930 |
> +-------------------------------------------------------------------+
>
> --
> filky
> ------------------------------------------------------------------------
> filky's Profile: http://www.excelforum.com/member.php...o&userid=11801
> View this thread: http://www.excelforum.com/showthread...hreadid=554906
>
>

Struggling a bit to make the code work but I get the idea. Thanks very much.

Struggling a bit to make the code work but I get the idea. Thanks very much.

Nel post news:filky.29unyo_1151057403.1205@excelforum-nospam.com
*filky* ha scritto:

> Hi,
> I have 6 worksheets with 65,536 lines filled in each one. There are
> five columns. The fourth and fifth are filled in but the first three
> are only filled in every 20 or 30 lines (the number is not
> consistent).
> I would like to be able to write a routine or formulae that could fill
> in the empty cells with the last bit of data so I have a full sheet to
> work with. For example,
>
> John 123 456 789 abc
> 523 def
> 566 ghi
> Fred 423 623 789 abc
> 523 def
> 566 ghi
>
> Needs to look like this;
>
> John 123 456 789 abc
> John 123 456 523 def
> John 123 456 566 ghi
> Fred 423 623 789 abc
> Fred 423 623 523 def
> Fred 423 623 566 ghi
>
> Formatting is a little off so I ave attached a screenshot if it helps.
> Any help would be much appreciated as this could be a very long
> exercise otherwise..!
>
> Thanks
>
> Paul.
>

Hi Paul,

select the range you want to fill, then press F5 click on Special, select
Blanks, then Ok. Without moving selection type = and then Arrow Up. Now Ctrl
+ Enter.
To save this, select the zone and make Copy & PastSpecial, Values.

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Very simply could you do something like
=IF(A2<>"";A2;B1)?

"Franz Verga" wrote:

> Nel post news:filky.29unyo_1151057403.1205@excelforum-nospam.com
> *filky* ha scritto:
>
> > Hi,
> > I have 6 worksheets with 65,536 lines filled in each one. There are
> > five columns. The fourth and fifth are filled in but the first three
> > are only filled in every 20 or 30 lines (the number is not
> > consistent).
> > I would like to be able to write a routine or formulae that could fill
> > in the empty cells with the last bit of data so I have a full sheet to
> > work with. For example,
> >
> > John 123 456 789 abc
> > 523 def
> > 566 ghi
> > Fred 423 623 789 abc
> > 523 def
> > 566 ghi
> >
> > Needs to look like this;
> >
> > John 123 456 789 abc
> > John 123 456 523 def
> > John 123 456 566 ghi
> > Fred 423 623 789 abc
> > Fred 423 623 523 def
> > Fred 423 623 566 ghi
> >
> > Formatting is a little off so I ave attached a screenshot if it helps.
> > Any help would be much appreciated as this could be a very long
> > exercise otherwise..!
> >
> > Thanks
> >
> > Paul.
> >
>
> Hi Paul,
>
> select the range you want to fill, then press F5 click on Special, select
> Blanks, then Ok. Without moving selection type = and then Arrow Up. Now Ctrl
> + Enter.
> To save this, select the zone and make Copy & PastSpecial, Values.
>
> --
> (I'm not sure of names of menues, option and commands, because
> translating from the Italian version of Excel...)
>
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>