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

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

Nel post
*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

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


--
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.


--
filky
------------------------------------------------------------------------
filky's Profile: http://www.excelforum.com/member.php...o&userid=11801
View this thread: http://www.excelforum.com/showthread...hreadid=554906

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

"Franz Verga" wrote:

Nel post
*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