Fill cells

[bill g]

In VBA, how do I code so that a macro will copy a cell contents to fill in
the rows before the next text. For example:

A B
1 tom
2
3 carl
4
5

I want to fill so it looks like this:

A B
1 tom
2 tom
3 carl
4 carl
5 carl

Thanks

[Chip Pearson]

Bill,

Here's a quick little procedure that will do this.

Dim Rng As Range
On Error Resume Next
For Each Rng In Selection
If Rng.Value = "" Then
Rng.Value = Rng(0, 1).Value
End If
Next Rng

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
http://www.cpearson.com/excel
ICQ # 39531874

bill g <su...@worldnet.att.net> wrote in message
news:7m3deh$c36$1...@bgtnsc03.worldnet.att.net...

[Werner Janz]

Hi Bill,

try (not tested)
------------------------
sub fill_empties

dim cur_rng as range

for each cur_rng in selection
if isempty(cur_rng) then
cur_rng.value=cur_rng.offset(-1,0).value
endif
end for

end sub
------------------------

HTH and bye
Werner
--
Bei Antwort bitte NOSPAM entfernen /
Please remove NOSPAM at response
--------------------------------------------------------------
bill g <su...@worldnet.att.net> schrieb in im Newsbeitrag:

[David McRitchie]

Hi Bill, (posted with email copy to bill and werner)
A couple of minor corrections to Werner's untested macro. On the Next
statement and a test that cell to be filled is not in Row 1 of spreadsheet.

Sub fill_empties()
' Subject: Re: Fill Cells
' From: "Werner Janz" <NOSPAMwe...@primus-online.de>
' Newsgroups: Microsoft.public.Excel.programming
' Date: Sat, 10 Jul 1999 13:30:13 +0200
Dim cur_rng As Range
For Each cur_rng In Selection
If IsEmpty(cur_rng) Then
If cur_rng.Row > 1 Then
cur_rng.Value = cur_rng.Offset(-1, 0).Value
End If
End If
Next cur_rng 'corrected from posting, also added row 1 test
End Sub

I have a similar macro that will not pull down values from out of range.
http://members.aol.com/dmcritchie/excel/fillempt.htm
Also see related area on fillempt.htm page.

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

>From: "Werner Janz" <NOSPAMwe...@primus-online.de>
>bill g <su...@worldnet.att.net> schrieb in im Newsbeitrag:
>> In VBA, how do I code so that a macro will copy a cell contents to fill in

>> the rows before the next text. For example: [clipped]