Auto-populate, Auto-copy or Auto-fill?

[Guest]

I'm trying to work with an export from a Lotus Notes db <grumble>. My first
column contains sporadic entries (with blank cells between), as each of the
column A entries are associated with column B entries in the rows where the
column A cell is empty. For example:

red
apple
firetruck
wagon
yellow
canary
lemon

If cell A1 is "red" in my example, how can I autopopulate A2-A4 to also be
"red" and A6-A7 to autopopulate with "yellow" based on that value in A5? Is
there some formular expression that I could use. I'm not VisualBasic
skilled, but could something simple be done there?

 

[Gord Dibben]

Select column A and F5>Special>Blanks>OK

Enter an = sign in active cell then point or arrow up to cell above and hit CTRL
+ ENTER


Gord Dibben MS Excel MVP

 

[Guest]

To populate it down, you could try the methods described in
either of these 2 excellent pages by:

1. Debra Dalgleish:
http://www.contextures.com/xlDataEntry02.html
Excel -- Data Entry -- Fill Blank Cells

Fill Blank Cells
Fill Blank Cells Programmatically**
**Sub FillColBlanks() by Dave Peterson

2. David McRitchie:
http://www.mvps.org/dmcritchie/excel/fillempt.htm
Fill in the Empty Cells

 

[Pete_UK]

You could get rid of the blanks in column B where there is a new
colour by first of all inserting a new cell in A1, so all the colours
move down one row. Then you could apply autofilter to column B to
select Blanks. Then you could highlight all the visible rows within
the filter range (although there will be nothing in them - use the
blue row identifiers as a guide) and Edit | Delete Row. Select All
from the filter pull-down in column B, and you should be left with
this:


red apple
firetruck
wagon
yellow canary
lemon

Then select the cell below "red" within this data and press F5 (or
Edit | GoTo), click on Special then click on Current Region. Then
press F5 again, click Special, then click Blanks -all the blanks in
column A should be highlighted, with the active cell being under the
cell containing "red".

Then type = and then click on the cell containing "red", and then use
CTRL-Enter to put this formula in all the blanks.

You will then need to fix the values, so highlight all the data in
column A, click <copy>, then Edit | Paste Special | Values (check) |
OK then <Enter>.

Than should give you what you want without any VBA.

Hope this helps.

Pete

 

[Guest]

Haven't tried these yet but certainly will (and wanted to reply before the
weekend)! Thank you all for the suggestions.