Add blank space to a cell if it's empty

  • Thread starter Thread starter MikeS
  • Start date Start date
M

MikeS

How can this be done in VBA?

Select some cells

For each cell, if the cell.contents = "" ; is empty

then set cell.contents = " " ; blank space

that's it

thanks!
 
Putting a space character in empty cells is usually a bad idea. If you have
formulas that use those cells, you may have to modify those formulas to treat
those cells differently.

But you could select the range you want and loop through the cells:


Option Explicit
Sub testme()
dim myRng as range
Dim myCell as range

set myrng = selection
for each mycell in myrng.cells
if mycell.value = "" then
mycell.value = " "
end if
next mycell
End sub

This will change formulas that evaluate to "" to that space character, too.
This may not be what you want.

But, again, I wouldn't do this. I don't think it's a good idea.
 
Putting a space character in empty cells is usually a bad idea.  If youhave
formulas that use those cells, you may have to modify those formulas to treat
those cells differently.

But you could select the range you want and loop through the cells:

Option Explicit
Sub testme()
  dim myRng as range
  Dim myCell as range

  set myrng = selection
  for each mycell in myrng.cells
     if mycell.value = "" then
        mycell.value = " "
     end if
  next mycell
End sub

This will change formulas that evaluate to "" to that space character, too.
This may not be what you want.

But, again, I wouldn't do this.  I don't think it's a good idea.
Click to expand...

You make good points. So my intent is clear, I want to add the blank
space to a specific column of cells so the auto-complete will continue
to work. I've found that Excel will not auto-complete if there are
empty cells above or below.

I've been manually adding spaces as needed, and my formulas account
for this.

thanks for the help - I sincerely appreciate it!
 
Hi,

Auto Complete also works if an adjactent column contain data so that there
is some data touching the cell you are entering into. Here's an example

C D
a Shane
a Marsh
a
a

If you start entering S in D4 autocomplete will work. So the general idea
is to create a dummy column adjacent to your data input column, hidden or
not, which contains data as far down as you will ever go.

Also, a slight modification to the VBA suggestion could be

For each cell in Selection
IF cell = "" : cell = " "
Next cell
 
I never noticed that. (But I hate autocomplete, so I turn it off <vbg>.)

But instead of using a macro, I'd just insert a (temporary) column and then fill
it with a's (as far as I need). Just by drag and dropping.
 
Back
Top