Insert a space in all blank cells of the first column

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

andreashermle

Dear Experts:

For some specific reasons I would like to enter a space in all blank
cells of the first column of my spreadsheet called sheet 1.

How is this done by using VBA?

Help is much appreciated. Thank you very much in advance. Regards,
Andreas
 
First, this is usually a mistake to do.

It'll mess up formulas like:
=if(a1="","it looks empty","it doesn't look empty")

If you have other formulas that use these empty cells--like:
='sheet 99'!a1
and you're seeing 0's where you don't want them, try modifying your formula:
=if('sheet 99'!a1="","",'sheet 99'!a1)
The receiving cell will look empty.

But if you want...
Record a macro when you
Select column A (or the range you want)
Edit|replace
what: (leave blank)
with: (spacebar character)
replace all.

ps. Edit|replace only works on the used range. So if you want those space
character cells past the last used row, put something column B of the last row
you want. Do the edit|replace and clear that cell in column B.
 
First, this is usually a mistake to do.

It'll mess up formulas like:
=if(a1="","it looks empty","it doesn't look empty")

If you have other formulas that use these empty cells--like:
='sheet 99'!a1
and you're seeing 0's where you don't want them, try modifying your formula:
=if('sheet 99'!a1="","",'sheet 99'!a1)
The receiving cell will look empty.

But if you want...
Record a macro when you
Select column A (or the range you want)
Edit|replace
what: (leave blank)
with: (spacebar character)
replace all.

ps.  Edit|replace only works on the used range.  So if you want thosespace
character cells past the last used row, put something column B of the last row
you want.  Do the edit|replace and clear that cell in column B.
Click to expand...


Dear Dave,

thank you very much for your professional help. It works. Thank you.
Regards, Andreas
 
andreashermle;582988 said:
Dear Experts:
Click to expand...
For some specific reasons I would like to enter a space in all blank
cells of the first column of my spreadsheet called sheet 1.
Click to expand...
How is this done by using VBA?
Click to expand...
Help is much appreciated. Thank you very much in advance. Regards,
Andreas
Click to expand...

Code:
--------------------
    Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = " "

--------------------

it is intelligent enough only to do this for the used range of the
sheet.
If this doesn't suit your purposes then specify the range:

Code:
--------------------

  For Each cll In Sheets("Sheet1").Range("A1:A200").Cells
  If IsEmpty(cll) Then cll.Value = " "
  Next cll

--------------------

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=161338

Microsoft Office Help
Click to expand...

Dear p45cal,

thank you very much for your professional help. It works fine.
Regards, Andreas
 
Back
Top