Cells look empty but are not (XP)

Topic

New Reply

I frequently receive spreadsheet files imported from other programs. It is not uncommon to have cells that look empty but are not. Whatever is in those cells plays havoc with formulas. I have attached a small sample. I would like to know what is really there and a simple method of cleaning the worksheet up or writing formulas that can accommodate the situation.

Thank you.

Reply | Quote

Replies

The cells contain a null string. Select the cells and run the macro below:


Public Sub FixNullStrings()
Dim oCell As Range
For Each oCell In Selection
If Not (oCell.HasFormula) And Trim(oCell.Value) = "" Then
oCell.Value = Trim(oCell.Value)
End If
Next oCell
End Sub


The code above will not fix cells that contain one or more blank characters. If you also want to remove those, then use this:


Public Sub FixNullStrings()
Dim oCell As Range
For Each oCell In Selection
If Not (oCell.HasFormula) Then
oCell.Value = Trim(oCell.Value)
End If
Next oCell
End Sub


Reply | Quote

This is dangerous if the seemingly blank cells are interspersed with cells containing formulas. Perhaps this alternative?

Sub FixBlanks()
Dim oCell As Range
For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Cells
If Trim(oCell.Value) = “” Then
oCell.Value = “”
End If
Next oCell
End Sub

Reply | Quote

You are absolutely correct, but your fix also has a problem. If you have a formula like this:

=IF(A2=””,””,”xx”)

that returns a null string, then your code will also delete the formula. I have edited my original post with a change that should fix that also.

Reply | Quote

Your code is fine, but mine too, I think. SpecialCells(xlCellTypeConstants) excludes cells with formulas, even if they result in a blank.

Reply | Quote

You are correct, sorry about that. I didn’t see the For statement, and I just copied your If statement and pasted it into my already existing routine to test.

Reply | Quote

My thanks to everyone! It’s gratifying to ask a question that results in this kind of response. It proves there is no neat, easy search & replace type response and I was right to ask for help! Thanks again.

Reply | Quote