Insert a space in all blank cells of the first column
 

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

Insert a space in all blank cells of the first column
 

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.

andreashermle wrote:

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

Insert a space in all blank cells of the first column
 

andreashermle;582988 Wrote:
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(xlCellTyp eBlanks).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/sh...d.php?t=161338

Microsoft Office Help

Insert a space in all blank cells of the first column
 

On 10 Dez., 16:37, Dave Peterson wrote:
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.

andreashermle wrote:

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


Dear Dave,

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

Insert a space in all blank cells of the first column
 

On 10 Dez., 17:12, p45cal wrote:
andreashermle;582988 Wrote:

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(xlCellTyp eBlanks).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/sh...d.php?t=161338

Microsoft Office Help

Dear p45cal,

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