Replace Empty Text/Space in all Empty Cells

Hi Experts,

How can i remove/replace all empty text/space in all the cell in a sheet? There's no visible data on the cell but when i use =COUNTA to check - it results into a number which proves that there are empty spaces/text in the cell.

Need the macro to only remove empty text/space on empty cells and not spaces on real values. I can use REPLACE but it also removes spaces from cells with true values.

Please help.

I loaded K14 with spaces and this cleared them:

Please Login or Register  to view this content.

Thank you xladept. It's not working though.

I'm copying cells from one sheet to another. The source formula in Sheet 1 Row 1 goes like this A1=IF(A2="","",A2) then i hit submit and it's copied to Sheet2.

Now if A2 is blank from sheet1 is blank - when its copied to sheet2 - the cell its copied to is visibly blank but when i use =COUNTA() to confirm it says 1.

THe only way to remove the unseen/empty text is go the cell and press DEL.

How can i override this? Is there a more efficient formula that will not copy empty cells from the source sheet?

Please help.

Do a COUNTA minus a COUNTBLANK.

Thank you AligW. but I'm just using =COUNTA to check.. the problem is when i view the copied data from Sheet 1 to Sheet 2 the cells are not really "empty".

It's copied to cells are visibly empty but there's a value in it that can only be removed by going to the cell and pressing DEL.

Need help.

I'm advising you to do this:

=COUNTA(range)-COUNTBLANK(range)

EDIT: Sorry - just realised you want a macro.

Hi AligW

Not sure how i could incorporate that into the code.

Maybe:

Please Login or Register  to view this content.

Or, for a more complete cleanup:

Please Login or Register  to view this content.