Insert a space in all blank cells of the first column

[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

 

[Dave Peterson]

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.

 

[p45cal]

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

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

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

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

Code

 

[andreashermle]

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.

Dear Dave,

thank you very much for your professional help. It works. Thank you.
Regards, Andreas

 

[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

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

Dear p45cal,

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