Replace invisible characters from a range of cells

[Zaigham]

Hi,
I've a table and used this type of commands e.g. "=IF(B13="","",$F$44)" in most of the cells. Resultantly there are many rows which appears blank. But when I copied the table and pasted it as value on other sheet and from the upper left corner say from cell A1 I pressed the Ctrl+ Down Arrow, the cursor went down to the vary last cell of the table, even though the last few rows were blank. I have not find any character in these visibly blank cells in edit mode by pressing F2 too.
Is there any way to replace all these invisible characters by using "Find and Replace" command?

Regards
Zaigham

 

[Alex Blakenburg]

On the basis that you have pasted values to another location and want to actually empty the cells with "".
Option 1) Manual method
If it is just a single column. You can use Text to Columns. Select delimited, turn off all the delimiters and then Finish and it should change those cells to empty.

Option 2) Vba
If you have a lot of columns impacted, select any cell in the data area and run this code.
(you could also put the line in the immediate window and hit enter)

Sub ClearEmptyTextStrings()
    ActiveCell.CurrentRegion.Value = ActiveCell.CurrentRegion.Value
End Sub

 

[Zaigham]

Hi Alex
VBA code work fine. Thank you very much for your kind help.
Regards