Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add blank space automatically in a column | New Users to Excel | |||
Macro to add column and insert date then in blank cells copy fromprevious cell | Excel Programming | |||
Insert blank space | Excel Programming | |||
Merge contents of cells and insert line space | Excel Worksheet Functions | |||
How can I insert a space before the last character in a range of cells | Excel Programming |