Auto-populate, Auto-copy or Auto-fill?
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
On Aug 10, 5:50 pm, Jay S. <Jay wrote:
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?
|