how to make all the Empty cells as a blank cells

[shaal]

Please find the below excel for understanding

Lets consider only the first 3 rows on the attached excel

I am reading the excel from a java file. so that the cells F2,G2,A3,B3 and E3 are taken as empty by which it is returning as "null". But for me it should taken as "".

this can be achieved by, writing some texts in the cells F2,G2,A3,B3 and E3 and deleting the values (texts).

This can be possible for 3 or 4 rows.. But how to make it possible for the whole sheet. Any idea..??

So that none of the empty cells should return as "null" but rather it should return as "" (blank values)..

Plz help.. Its very critical and have to fix it..

 

[Rick Rothstein]

Lets consider only the first 3 rows on the attached excel

You cannot attach files to postings in this forum, so if you want us to see what your Excel file looks like, you will have to post it online for us to download... either from your SkyDrive if you have one or else from one of the free file-sharing website on the internet (such as http://www.box.net/files - be sure to post the URL they give you for your file).

 

[p45cal]

I have no idea whether this will work, but try selecting the whole region you want to alter in this way and execute this line of code:
selection.specialcells(xlcelltypeblanks).value =""

Maybe the same could be done without code by:
Selecting the area you want to alter
Pressing F5
Choosing Special…
Choosing Blanks
OK
Do not alter the selection at this point!!
type a single letter and hold down CTRL and press Enter
Do not alter the selection at this point either!!
Press F2
Press backspace, to delete the letter, then hold down CTRL and press Enter.

That should do what you do, wholesale, on the blank cells.

 

[shaal]

Please find the below url for the thread in detail.

how to make all the Empty cells as a blank cells

 

[patel45]

You can put a space on blank cells with this code

Sub b()
Dim cell As Range
For Each cell In Range("A1:A10") ' <<<< to be changed
  If IsEmpty(cell) Then
    cell.Value = " "
  End If
Next
End Sub

 

[shaal]

Hi patel,

Can u explain me where to write this code in excel as I am new to it.

 

[p45cal]

Hi patel,

Can u explain me where to write this code in excel as I am new to it.

While you can see the sheet in question in Excel, select the area you want to alter, then press Alt+F11
A new window will open.
Press Ctrl+G which will bring up the Immediate Pane (if it's not already open).
In there, type or paste the following;
selection.specialcells(xlcelltypeblanks).value =""
and press Enter (the flashing text cursor should be somewhere on that line before you press Enter).
Nothing much visibly will happen (hopefully no error), but see if that has solved the Null cells problem.

If it has and you need to do this a lot we can make a macro to do it, otherwise you can try to do it manually as I suggested in my previous post.

edit post posting: Oops, I see that you addressed this to patel. May I caution against putting a single space inside large numbers of cells.