Fill cells

  • Thread starter Thread starter Ivor Williams
  • Start date Start date
I

Ivor Williams

I have a spreadsheet with approximately 1500 rows. In cell A1 is an entry,
cells A2 through A6 are blank, there is an entry in A7, cells A8 through A11
are blank and so on. The number of blank cells between entries varies. Is
there a function, or can I create a macro to fill the blank cells with the
value in the cell above?

Ivor
 
I read the item in contextures with interest. I often have this need as
described there. The amazing thing is that it is possible to get a "no
cells were found", even if the cell is blank (hitting F2 on the cell will
show the cursor all the way to the left). In those case I like to add a
column C (if the original was B) and put the formula =IF(B2="",B1,B2) this
formula is not bothered by "no cells were found". Copy/paste values, will
lock it in place and the original 'B' column will be deleted.

Maybe someone can enlighten me why the formula works but the
'Edit-Goto-blanks' does not? It does work once I use F2/enter on the cell.
I get my Excel list from another program and I cannot control the empty cell
format if there is one.
 
Those cells may have contained empty strings ( "" ), that were copied,
and pasted as values. This leaves a remnant in the cell, that is
displayed as a apostrophe, if you turn on Tools>Options, Transition tab,
Transition Navigation.

To clear the cells, you can try the Edit/Replace technique that Dave
Peterson describes here:

http://groups.google.ca/groups?&[email protected]
 
First, verify that there isn't a space bar (or some other character) in that
cell.

Use a formula like:
=len(a1)

If it comes back with something bigger than 0, then maybe you have spaces (or
HTML non-breaking spaces) in that cell.

Chip Pearson has a nice addin that can help you determine each character in a
cell:
http://www.cpearson.com/excel/CellView.htm

If it turns out that you have spaces (or those non-breaking spaces), David
McRitchie has some code that will help clean this junk up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

====

If the =len() formula returns a 0, then my bet is that you used to have a
formula in those "blank" cells. They evaluated to "".

like: =if(a1=3,"",a1+7)

Then you did a copy|paste special|values.

This leaves the cell looking blank, but it's not.
(Try =isblank() against one of the offending cells.)

And you can see the "detritus" left behind by toggling a setting.

Tools|options|transition tab|check transition navigation keys.

You'll see an apostrophe in the formula bar with that cell selected.

===
I like this way to clean up that type of "blank" cell.

select the range (or the whole sheet)
edit|replace
what: (leave blank)
with: $$$$$ (some unique value--not used!)
Replace all

Then reverse it:
edit|replace
what: $$$$$ (that same value)
with: (leave blank)
Replace all

Then the technique at Debra's site will work fine.
 
I've verified that, in fact, the cells are blank. When I run the macro, the
blank cells are filled with =R[-1]C. I see in the code where this comes
from, but not being very familiar with Excel VBA, I don't understand why I
get this result. I thought that it was because =R[-1]C sas enclosed in
quotes, but removing the quotes caused a problem with the code. Not sure
where to go from here.

Ivor
 
Make sure those cells are empty.

Then select that column and hit Edit|goto|special|blanks
then format|cells|General

It sounds like those cells are formatted as text.

If the formula still shows up, maybe it's because you have formulas turned on
under:
tools|options|View tab

Ivor said:
I've verified that, in fact, the cells are blank. When I run the macro, the
blank cells are filled with =R[-1]C. I see in the code where this comes
from, but not being very familiar with Excel VBA, I don't understand why I
get this result. I thought that it was because =R[-1]C sas enclosed in
quotes, but removing the quotes caused a problem with the code. Not sure
where to go from here.

Ivor

Dave Peterson said:
First, verify that there isn't a space bar (or some other character) in
that
cell.

Use a formula like:
=len(a1)

If it comes back with something bigger than 0, then maybe you have spaces
(or
HTML non-breaking spaces) in that cell.

Chip Pearson has a nice addin that can help you determine each character
in a
cell:
http://www.cpearson.com/excel/CellView.htm

If it turns out that you have spaces (or those non-breaking spaces), David
McRitchie has some code that will help clean this junk up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

====

If the =len() formula returns a 0, then my bet is that you used to have a
formula in those "blank" cells. They evaluated to "".

like: =if(a1=3,"",a1+7)

Then you did a copy|paste special|values.

This leaves the cell looking blank, but it's not.
(Try =isblank() against one of the offending cells.)

And you can see the "detritus" left behind by toggling a setting.

Tools|options|transition tab|check transition navigation keys.

You'll see an apostrophe in the formula bar with that cell selected.

===
I like this way to clean up that type of "blank" cell.

select the range (or the whole sheet)
edit|replace
what: (leave blank)
with: $$$$$ (some unique value--not used!)
Replace all

Then reverse it:
edit|replace
what: $$$$$ (that same value)
with: (leave blank)
Replace all

Then the technique at Debra's site will work fine.
Click to expand...
Click to expand...
 
Works perfectly! Thanks for the help.

Ivor

Dave Peterson said:
Make sure those cells are empty.

Then select that column and hit Edit|goto|special|blanks
then format|cells|General

It sounds like those cells are formatted as text.

If the formula still shows up, maybe it's because you have formulas turned
on
under:
tools|options|View tab

Ivor said:
I've verified that, in fact, the cells are blank. When I run the macro,
the
blank cells are filled with =R[-1]C. I see in the code where this comes
from, but not being very familiar with Excel VBA, I don't understand why
I
get this result. I thought that it was because =R[-1]C sas enclosed in
quotes, but removing the quotes caused a problem with the code. Not sure
where to go from here.

Ivor

Dave Peterson said:
First, verify that there isn't a space bar (or some other character) in
that
cell.

Use a formula like:
=len(a1)

If it comes back with something bigger than 0, then maybe you have
spaces
(or
HTML non-breaking spaces) in that cell.

Chip Pearson has a nice addin that can help you determine each
character
in a
cell:
http://www.cpearson.com/excel/CellView.htm

If it turns out that you have spaces (or those non-breaking spaces),
David
McRitchie has some code that will help clean this junk up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

====

If the =len() formula returns a 0, then my bet is that you used to have
a
formula in those "blank" cells. They evaluated to "".

like: =if(a1=3,"",a1+7)

Then you did a copy|paste special|values.

This leaves the cell looking blank, but it's not.
(Try =isblank() against one of the offending cells.)

And you can see the "detritus" left behind by toggling a setting.

Tools|options|transition tab|check transition navigation keys.

You'll see an apostrophe in the formula bar with that cell selected.

===
I like this way to clean up that type of "blank" cell.

select the range (or the whole sheet)
edit|replace
what: (leave blank)
with: $$$$$ (some unique value--not used!)
Replace all

Then reverse it:
edit|replace
what: $$$$$ (that same value)
with: (leave blank)
Replace all

Then the technique at Debra's site will work fine.


HansM wrote:

I read the item in contextures with interest. I often have this need
as
described there. The amazing thing is that it is possible to get a
"no
cells were found", even if the cell is blank (hitting F2 on the cell
will
show the cursor all the way to the left). In those case I like to add
a
column C (if the original was B) and put the formula =IF(B2="",B1,B2)
this
formula is not bothered by "no cells were found". Copy/paste values,
will
lock it in place and the original 'B' column will be deleted.

Maybe someone can enlighten me why the formula works but the
'Edit-Goto-blanks' does not? It does work once I use F2/enter on the
cell.
I get my Excel list from another program and I cannot control the
empty
cell
format if there is one.
--
HansM

There are instructions on the following page, for filling blank
cells:

http://www.contextures.com/xlDataEntry02.html

Ivor Williams wrote:
I have a spreadsheet with approximately 1500 rows. In cell A1 is an
entry, cells A2 through A6 are blank, there is an entry in A7,
cells
A8
through A11 are blank and so on. The number of blank cells between
entries varies. Is there a function, or can I create a macro to
fill
the
blank cells with the value in the cell above?

Ivor
Click to expand...
Click to expand...
Click to expand...
 
Back
Top