CODEWITHSUNDEEP
vbaexcelexcel-2010
ygor jesus
ygor jesus

Reputation: 27

Blanks Cells in Excel

I got a sheet that came from a query (get external data), but this sheet have many "empty" cells. I put this way, "empty", because when I gave the command to put all empty cells equals zero, a lot of cells, aparentment empty, did not turn into zero. The Excel did not recognize these cells as empty cells, although do not have anything into.

Can anyone knows what kind of magic is envolved in this?

Thanks!

Upvotes: 3

Views: 391

Answers (2)

Eduardo Poço
Eduardo Poço

Reputation: 3079

Maybe some unprintable char. Add a module with this sub:

Sub show_content()
    cont = Selection.Value
    If cont <> "" Then
        MsgBox Asc(cont)
    Else
        MsgBox "Cell really is a blank!"
    End If
End Sub

Then, select the seemingly blank cell and run 'show_content' on the macro menu (alt+F8). It will show you the ASCII value of the strange char, if it is not a blank, or confirm it is really a blank (empty cell).

This one will erase cells with blank spaces, so they will become really empty and be recognized as zero by the formulas:

Sub erase_spaces()
    For Each elem In ActiveSheet.UsedRange
        elem.Value = Trim(elem.Value)
    Next elem
End Sub

Upvotes: 1

radiocontrolled
radiocontrolled

Reputation: 517

Upon finding the character(s) in the cell that appears blank, you can run a "Find and Replace" (Ctrl+F) to change all cells with the certain character(s) to "0".

Enter the character(s) in the "Find What" input box and a "0" in the "Replace With" input box. This will allow you to find and replace all cells that contain the character(s) with a "0" or whatever you would like.

Upvotes: 0

Related Questions